Single quote _'_ embedded in field prevents successful SQL query

Question

I use a commercial data base that is delivered with fields such as:

Super Crew 5 1/2' Bed 4WD  // where single quote (') symbolizes feet

When this field is required to be used in an SQL query, it expands out similar to this excerpt:

...WHERE TitleIndex.Title='Super Crew 5 1/2' Bed 4WD'

Resulting in an error message query expression missing operator. The portion of the intended string literal >>> Bed 4WD'<<< is not understood because of the single quote preceding it.

Note: If I manually edit the database field to replace >'< with >ft.<:

'Super Crew 5 1/2ft. Bed 4WD'

the query works. But it will not be practical to edit all fields containing single quotes each time a new database is delivered.

I have looked at parametrization, but that will not work as the field would still require the single quote embedded in the field be escaped with another single quote:

SET @Value = 'Super Crew 5 1/2'' Bed 4WD'

Again, this requires editing, which is not an option for us.

Since I cannot escape a single quote embedded in a provided field, is there a way I can package it into a query string so that it will not be rejected as an illegal query?

EDIT (to show function problem exists in)

Note, in the code below, char *title is obtained in a previous call, it contains the column data just as it is stored in the database. >>>Super Crew 5 1/2' Bed 4WD<<<

int AMSDB_API ams_get_tid_from_title(char *title, int *titleId)
{
    int index, status, iStatus, tid;
    char query[500];

    sprintf (query, "SELECT TitleIndex.TitleId FROM TitleIndex WHERE TitleIndex.Title=\'%s\'", title);
    hstmt = DBActivateSQL(hAmsIndex, query);

    //numberRecords = DBNumberOfRecords(hstmt); 

    status = DBBindColInt (hstmt, 1, &tid, &iStatus);
    DBFetchNext(hstmt);
    DBDeactivateSQL(hstmt);
    *titleId = tid;

    return status;
}

`WHERE TitleIndex.Title='Super Crew 5 1/2\' Bed 4WD'`. You need to escape the `'` (`\'`). — Ben, Aug 04 '14 at 16:07
Parametrization is the correct solution and it will work if you do it correctly. Therefore, you need to show us what you have tried (in *code*), and also let us know what DBMS this is (as parametrization varies between platforms). — RBarryYoung, Aug 04 '14 at 16:08
@Ben - Requires editing. Read the reasons I gave for not being able to do that. — ryyker, Aug 04 '14 at 16:09
@RBarryYoung - Can you tell me specifically what part is missing. The line _...WHERE TitleIndex.Title='Super Crew 5 1/2' Bed 4WD'_ is really the crux of the problem. I read that field from column data in the database in a prior call, into a string variable, then attempt to use it in another query. Tell me what part of my code you would like to see, and I will edit my post. — ryyker, Aug 04 '14 at 16:15
@RBarryYoung - Would Parametrization not require that at some point I edit the problem piece of column data before placing it into parameters? — ryyker, Aug 04 '14 at 16:16
Use parametrisation. You provide the query, with placeholders for where the values go, and the DBM does the rest, escaping and quoting where necessary. — MRAB, Aug 04 '14 at 16:19
Most probably you have to use some function before the insert command. Something to sanitize your input string. Something like this: http://stackoverflow.com/questions/11416125/escaping-unwanted-characters-mainly-single-quotes-replace-function-and-implem — Jorge Campos, Aug 04 '14 at 16:19
@RBarryYoung _ will edit my post, but as I said, I have considered parametrization, but do not yet understand how it will apply in my situation. Pls, one minute, I will add more to post. — ryyker, Aug 04 '14 at 16:25
And no, proper parametrization would *only* require escaping data if it is hard-coded in your program somewhere. Since this is variable data coming from a "delivered database", there is so far no reason why that should ever happen. Read it from the DB as a variable, then provide it to your queries as a parameter variable and *NOT* as text that you are trying to inject into your SQL commands. — RBarryYoung, Aug 04 '14 at 16:27
@RBarryYoung - _Read it from the DB as a variable, then provide it to your queries as a parameter variable and NOT as text that you are trying to inject into your SQL commands_. See my edit, is this _not_ doing what you suggested already? If not, Can you provide a small example of how to do that. If being in a _parameter_ will cause it to be processed differently than as a concatenated string, I will use it. — ryyker, Aug 04 '14 at 16:31
WHERE TitleIndex.Title='Super Crew 5 1/2'' Bed 4WD' To escape a single quote in SQL, it needs another single quote in front of it. https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&es_th=1&ie=UTF-8#newwindow=1&q=sql%20single%20quote — gnomed, Aug 04 '14 at 16:35
@RBarryYoung - thanks for the edit, but I am not using VBA. NI LabWindows/CVI (an ANSI C environment with extended libraries, including query) — ryyker, Aug 04 '14 at 16:38
@gnomed - editing data from existing field is not an option. Single quote is included in that field already. — ryyker, Aug 04 '14 at 16:42
I am not asking you to edit data in a field. You have the title as a char* in your function. Scrub the string and be done with it. How is this an issue? — gnomed, Aug 04 '14 at 16:43
@R.. - why did you edit the post to eliminate the C tag. Does it not matter that I am using C? — ryyker, Aug 04 '14 at 17:59
@gnomed - I think I see what you are saying, modify the string, using standard C string techniques, before including it in the query by inserting an additional single quote. Actually, this is probably about the best suggestion I have seen. If you care to put it into a simple answer, I will accept it. — ryyker, Aug 04 '14 at 18:12
@ryyker precisely what I was suggesting. Answer added, but no pressure. — gnomed, Aug 04 '14 at 18:57
@ryyker: The question came up under the C tag for me, and nothing in the content seemed to pertain to C or be something that a person following the C tag would have any reason to have the knowledge to answer. Now that you've added the C code to the question, I think it's appropriate to have the C tag, and the issue is just a simple SQL injection problem (don't use `%s` with literals to make SQL queries). — R.. GitHub STOP HELPING ICE, Aug 04 '14 at 19:05
@R.. - Thanks. Sorry for the confusion, my mistake. (I thought the C code was there before the tag.). regarding your comment _don't use %s with literals to make SQL queries_ is precisely my problem. The question remains, how then shall I solve the problem? From what I have read using parametrized methods are still an issue. The only direction I see is to modify the string as gnomed has suggested. — ryyker, Aug 04 '14 at 19:33

score 2 · Accepted Answer · answered Aug 04 '14 at 18:56

2

As you know, to use a single quote within a query string, it needs to be escaped with another single quote.

As the simplest/fastest to implement solution, you can "escape" the title string in your C function. You have a reference to the search string (title) as a char * with this string reference you can find any single quotes and escape them with another single quote. Then use this modified string in your query.

Sorry I am not providing code, C is not my strong point.

answered Aug 04 '14 at 18:56

gnomed

5,483
2
26
28

As far as I can tell, this is my only path. Admittedly, I am not seasoned with SQL syntax, or techniques. But I am capable of modifying a C string to remove any offending embedded syntax before using it in a query. Regarding _C is not my strong point_, We are even, SQL syntax/technique is not mine. Thanks again. – – ryyker Aug 04 '14 at 19:47

score 0 · Answer 2 · answered Aug 04 '14 at 16:42

OK, I see that this is MS-Access and yes, Access does make it inordinately hard to do proper SQL parameterization. The Microsoft doc does explain it here though: http://msdn.microsoft.com/en-us/library/dd671279(v=office.12).aspx

In short what you have to do is to put your variable value into a Global or Module level variable.

Public pstrParameter1 As String

Then you have to write a publicly visible VBA function that will return that global/module variable's value.

Public Function Parmeter1Value() As String
    Parmeter1Value = pstrParmeter1
End Function

Finally, your Access SQL query must call that function to supply the variable value to SQL.

...WHERE TitleIndex.Title=Parameter1Value()

Kludgy, but thats MS-Acces for you.

In my earlier comments, I responded to the RDBMs? question with _Microsoft Access, and I am using an implementation of database calls from National Instruments, LabWindows/CVI (an ANSI C environment with extended libraries, including query)_. VBA will not work for me. I think I am limited to using C functions to modify the string variable I get on a previous query, to either change the ' to a ft. symbol, or exclude it with an additional ' before using it on any subsequent queries. Thanks for your help. — ryyker, Aug 04 '14 at 18:16

Single quote _'_ embedded in field prevents successful SQL query

2 Answers2