XML data type doesn't seem to be quoting parameters properly on insert #177
Description
When we use the latest Npgsql with a parameter to insert a row that has a "'" character in a column of the XML data type we're getting an error where it appears the "'" character isn't properly quoted.
I'm guessing the problem is here:
https://github.com/npgsql/Npgsql/blob/master/Npgsql/NpgsqlTypes/NpgsqlTypesHelper.cs#L442
where the line that reads
nativeTypeMapping.AddType("xml", NpgsqlDbType.Xml, DbType.Xml, true);
probably also needs some conversion function defined, like possibly this:
nativeTypeMapping.AddType("xml", NpgsqlDbType.Xml, DbType.Xml, true,
BasicNativeToBackendTypeConverter.StringToTextText);
but I couldn't figure out how to compile npgsql under mono so can't figure out how to test it. (the old instructions that suggest running "nant tests" aren't working for me, and I didn't find new ones)
Anyway --- here's a small test program that shows the problem.
It assumes a table where the PrecisionXML column is of the postgres XML type.
Thanks,
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.Text;
using Npgsql;
namespace npgsqltest
{
class Program
{
static void Main(string[] args)
{
string sXML = null;
Int32 iPrimaryKey = -1;
string sSQL = null;
string sErr = null;
DbConnection conn = null;
DbCommand cmd = null;
DbParameter prm = null;
DbDataAdapter da = null;
string sConnStr = "server=#.#.#.#; port=####; database=mydb; user id=myid; password=mypwd; Pooling=True;MinPoolSize=1;MaxPoolSize=5";
conn = new NpgsqlConnection(sConnStr);
conn.Open();
cmd = new NpgsqlCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
da = new NpgsqlDataAdapter();
da.SelectCommand = cmd;
prm = cmd.CreateParameter();
prm.DbType = DbType.String;
prm.Size = 200;
prm.ParameterName = "@LocationFullText";
prm.Value = "somelocationfulltextvalue";
cmd.Parameters.Add(prm);
sXML = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> <strings type=\"array\"> <string> this is a test with ' single quote </string></strings>";
prm = cmd.CreateParameter();
prm.DbType = DbType.Xml; // To make it work we need to use DbType.String; and then CAST it in the sSQL: cast(@PrecisionXML as xml)
prm.ParameterName = "@PrecisionXML";
prm.Value = sXML;
cmd.Parameters.Add(prm);
sSQL = "insert into GlobalLocation(LocationFullText, PrecisionXML, xcoordinate, ycoordinate)" +
" values(@LocationFullText, @PrecisionXML, -1, -1)" +
" returning GlobalLocationID";
cmd.CommandText = sSQL;
iPrimaryKey = (int)Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
conn.Dispose();
cmd.Dispose();
da.Dispose();
}
}
}