XML data type doesn't seem to be quoting parameters properly on insert #177

Closed
ramayer opened this Issue Mar 1, 2014 · 1 comment

Projects

None yet

2 participants

@ramayer
ramayer commented Mar 1, 2014

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();
        }
    }
}

@franciscojunior franciscojunior added this to the 2.2 milestone Apr 8, 2014
@franciscojunior franciscojunior added a commit to franciscojunior/Npgsql that referenced this issue Apr 9, 2014
@franciscojunior franciscojunior Fix #177. Add proper quoting to xml datatype. f26d852
@franciscojunior
Member

Hi, @ramayer !

I just created a fix for this problem. You were in the right track. I just needed to add some adjustments. In fact, Npgsql will handle xml data just like string data type regarding how it will encode and quote data.

Sorry for outdated build instructions. You would need to compile Npgsql with Xamarin Studio or use xbuild. I'll update the build instructions. Thanks for the heads up!

If it is possible for you to test the fix, it would be great!

Thanks in advance.

@franciscojunior franciscojunior added a commit to franciscojunior/Npgsql that referenced this issue Jun 9, 2014
@franciscojunior franciscojunior Fix #177. Add proper quoting to xml datatype. ffda7a6
@Emill Emill added a commit to Emill/Npgsql that referenced this issue Jun 13, 2014
@franciscojunior @Emill franciscojunior + Emill Fix #177. Add proper quoting to xml datatype. 314e5b8
@franciscojunior franciscojunior added the bug label Jul 22, 2014
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment