CODEWITHSUNDEEP
c#sqlpostgresql
CSharpened
CSharpened

Reputation: 12574

Programatically inserting rows where field value is SQL statement

I am currently working on a solution in C# to copy database tables across from Oracle to PostgreSQL. Everything is working great apart from one thing. When I get to copying one of my table which contains sql statements in one of its fields it falls over due to having two single quotes back to back. The SQL statements MUST remain in the table as it is being used to make another program database agnostic.

Is there a way to write the following SQL but without having the two single quotes back to back as seen near the 'TRUE' value near the end of the line. I have also included my code below to show how the statement is built up in C#. The column is a varchar2 in Oracle and a TEXT column in PostgreSQL.

EDIT: The sql example shown is the actual INSERT statement generated by my C# code which will then be run on the postgresql database to add a record to a table. It will be used to insert a text field, among others, that contains an sql statement in the form of a string. 

INSERT INTO SQL_FACTORY_TEST (SQL_FACTORY_TEST_ID,SQL_FACTORY_DIALECT,SQL_FACTORY_QUERY_NAME,SQL_FACTORY_SQL_COMMAND,USER_NAME) 
VALUES (21, 'ORACLE', 'GET_CLUSTERS', 'SELECT CLUSTER_ID, NUM_POINTS, FEATURE_PK, A.CELL_CENTROID.SDO_POINT.X, A.CELL_CENTROID.SDO_POINT.Y, A.CLUSTER_CENTROID.SDO_POINT.X, A.CLUSTER_CENTROID.SDO_POINT.Y, TO_CHAR (A.CLUSTER_EXTENT.GET_WKT ()),  TO_CHAR (A.CELL_GEOM.GET_WKT ()), A.CLUSTER_EXTENT.SDO_SRID FROM (SELECT CLUSTER_ID, NUM_POINTS, FEATURE_PK, SDO_CS.transform (CLUSTER_CENTROID, 4326) cluster_centroid, CLUSTER_EXTENT, SDO_CS.transform (CELL_CENTROID, 4326) cell_centroid, CELL_GEOM FROM :0) a  where sdo_filter( A.CELL_GEOM, SDO_CS.transform(mdsys.sdo_geometry(2003, :1, NULL, mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(:2, :3, :4, :5)),81989)) = 'TRUE'', 'PUBLIC')

Code sample: 

oleDataBaseConnection.OleExecutePureSqlQuery("SELECT * FROM " + tableName);

    if (oleDataBaseConnection.HasRows())
    {
        while (oleDataBaseConnection.NextRecord())
        {
            Dictionary<string, string> postgreSQLQueries = TypeConversion.GetQueryDictionary("POSTGRESQL");

            string postgreSQLInsertQuery;

            postgreSQLQueries.TryGetValue("INSERT", out postgreSQLInsertQuery);

            postgreSQLInsertQuery = postgreSQLInsertQuery.Replace("{0}", tableName);

            StringBuilder postgresQuery = new StringBuilder();

            postgresQuery.Append(postgreSQLInsertQuery);

            postgresQuery.Append("(");

            int columnCounter = 0;

            //add a column parameter to query for each of our columns
            foreach (KeyValuePair<string, string> t in columnData)
            {
                postgresQuery.Append(t.Key + ",");
                columnCounter++;
            }

            postgresQuery = postgresQuery.Remove(postgresQuery.Length - 1, 1);
            postgresQuery.Append(") ");

            postgresQuery.Append("VALUES (");

            //Loop through values and 

            for (int i = 0; i < columnCounter; i++)
            {
                string[] foo = new string[columnData.Count];
                columnData.Values.CopyTo(foo, 0);

                if (foo[i].ToUpper() == "TEXT")
                {
                    postgresQuery.Append("'" + oleDataBaseConnection.GetFieldById(i) + "', ");
                }
                else
                {
                    postgresQuery.Append(oleDataBaseConnection.GetFieldById(i) + ", ");
                }
            }

            postgresQuery = postgresQuery.Remove(postgresQuery.Length - 2, 2);
            postgresQuery.Append(") ");
            postgresSQLDBConnection.PostgreSQLExecutePureSqlNonQuery(postgresQuery.ToString());
        }
    }

Upvotes: 2

Views: 1281

Answers (3)

GarethD
GarethD

Reputation: 69759

I think the best approach is to use a parameterised insert query. so you are actually building a query like:

INSERT INTO [Table] VALUES (@Column1, @Column2, @Column3)

then passing the variable into the query. Something along the lines of:

List<OleDbParameter> parameters = new List<OleDbParameter>();
            for (int i = 0; i < columnCounter; i++)
            {
                postgresQuery.Append(string.Format("@Column{0}, ", i));
                parameters.Add(new OleDbParameter(string.Format("@Column{0}, ", i), oleDataBaseConnection.GetFieldById(i));
            }

Then pass paremeters.ToArray() to you OleDbCommand when executing. This means c# will do all the escaping, and work out the data type for you. It may need a bit of tweaking to suit your needs but the general gist of it is there.

Upvotes: 1

Simon Richter
Simon Richter

Reputation: 29578

You should always properly escape and unescape values stored in the database.

The PostgreSQL client library provides the PQescapeLiteral function for this purpose.

Upvotes: 0

user330315
user330315

Reputation:

The best solution is to use a PreparedStatement where you don't pass literals directly. I don't know C#, so I can't give you an example for that.

However if you have to keep the statement like that, you can use a "dollar quoted" string literal in PostgreSQL.

INSERT INTO SQL_FACTORY_TEST (SQL_FACTORY_TEST_ID,SQL_FACTORY_DIALECT,SQL_FACTORY_QUERY_NAME,SQL_FACTORY_SQL_COMMAND,USER_NAME) 
VALUES (21, 'ORACLE', 'GET_CLUSTERS', $$ ...... 'TRUE'$$, 'PUBLIC')

The $$ replaces the single quote(s) around the literal. If there is a chance that your value contains $$ you can also add some unique identifier to it, e.g. 

$42$String with embedded single quotes ''' and two $$ dollar characters$42$

Upvotes: 1

Related Questions