1

Not to get into a discussion about SQL injection and sanitizing and etc...

For various reasons, I am doing the unspeakable and building and executing a dynamic SQL query WITHOUT using query params.

So... I'm building a list of columns and a list of values, and then I have the following code:

insertRecord.setSql("INSERT INTO MyTable(" & columns & ") VALUES(" & values & ")");

This works properly for most records, but for a few records one of the string field values has an apostrophe or single quote:

'SHERIFF'S OFFICE'

This is giving me the SQL error: Incorrect syntax near 'S'.

I have tried using preserve single quotes in several ways and it doesn't help:

insertRecord.setSql("INSERT INTO MyTable(" & columns & ") VALUES(" & preserveSingleQuotes(values) & ")");

OR

insertRecord.setSql(PreserveSingleQuotes("INSERT INTO MyTable(" & columns & ") VALUES(" & values & ")"));

Is there any way to escape all the apostrophes, or otherwise format my sql string properly, other than using a ReplaceNoCase(colValue, "'", "''") on each value individually as I'm building the string (which does work)?

froadie
  • 79,995
  • 75
  • 166
  • 235
  • 1
    Can't you escape it with a backslash ie `'SHERIFF\'S OFFICE'`? – Cyclonecode Mar 10 '15 at 10:24
  • @Cyclone - You can escape it with doubled single quotes, but the values are coming from a dynamic source so I would need to do a replace to start adding it to each value. I'm looking for a built-in function that would sort of sanitize/format my sql statement before passing it to sql server, similar to what cfqueryparam does. – froadie Mar 10 '15 at 10:26
  • Did you try doing a single replacement of all the apostrophe's after the entire string is processed, or will that mess up single quotes surrounding string values? – Dan Bracuk Mar 10 '15 at 12:04
  • 2
    What you are attempting to do is such a bad idea (and you recognize this), I don't even want to help you be successful in your endeavor to implement it. I have seen ad-hoc queries for running `select` queries, but never for running `insert` queries. You are setting yourself up for a nightmare of support when things go wrong...and they will go wrong. – Scott Stroz Mar 10 '15 at 13:04
  • 1
    *You are setting yourself up for a nightmare of support* Absolutely. The choice is yours, but I have worked with apps that make heavy use of dynamic sql. Constructing and debugging those queries was *always* more of a hassle than with parametrized sql. Case in point, this question. – Leigh Mar 10 '15 at 17:13
  • Which version of ColdFusion? Oh, and I agree with @Leigh, and your own trepidation suggesting you are aware this is likely a foolhardy exercise from the outset. – Adam Cameron Mar 11 '15 at 20:01

1 Answers1

1

I'm not sure of the syntax for the setSql statement but perhaps you are missing single quotes around your string values. Have you tried adding those?

What I mean is this. Assuming the SQL statement needs to look something like this (notice the quotes around each value):

INSERT INTO MyTable 
(column1, column2, column3) 
VALUES 
('value 1', 'value 2', 'value 3')

Then the setSql statement should look like:

insertRecord.setSql("INSERT INTO MyTable (column1, column2, column3) VALUES ('value 1', 'value 2', 'value 3')");

So you could try something like (notice the single quotes around the values):

insertRecord.setSql("INSERT INTO MyTable(" & columns & ") VALUES('" & preserveSingleQuotes(values) & "')");

You might also need to loop over every values in order to enclose each within the single quotes.

Miguel-F
  • 13,450
  • 6
  • 38
  • 63
  • This doesn't really address the apostrophe problem. – Dan Bracuk Mar 10 '15 at 14:32
  • The `preserveSingleQuotes()` function addresses the apostrophe problem. I am suggesting that the `values` may need to be included within single quotes for the `setSql` statement. – Miguel-F Mar 10 '15 at 15:44
  • I haven't done so recently, but I have vague recollections of being unsuccessful with this approach. – Dan Bracuk Mar 10 '15 at 18:57
  • I very secure solution is to replace ' against \' with the replace function and then - for advances security reasons - do a rereplace with a regex. we often use something like ...(don't do query)... ... do query... having application.securecharacters = "[a-zA-Z0-9\+\_\-\ &\(\)\/öäüÖÄÜîôâéèÉÈûëçÇÿûÛùÙôœÔÏïÇæàÀß,\xfe]+" this only allows the characters in your securitystring ;-) – Raffael Meier Mar 09 '18 at 18:43
  • the basic concept should always be disallow all and allow specific, not allow all and exclude specific. – Raffael Meier Mar 09 '18 at 19:10