CODEWITHSUNDEEP
sqlsql-servervb.net
erasmo carlos
erasmo carlos

Reputation: 682

VB.Net: Escape singles quotes in query string that uses a variable with single quotes

A few of the records that my program is processing are creating a record in the log table that reads:

instruments: Incorrect syntax near 's'. Unclosed quotation mark after the character string ')))'.

The code uses an inline query that concatenates the value of a string variable to the WHERE clause in the SELECT and SubQueries.

Code: 

SQL = "SELECT instrument_id, description, sub_category_of, meta_instrument" _
& " FROM instruments " _
& " WHERE  (instrument_id IN " _
& " (SELECT instrument_id " _
    & "FROM instruments " _
    & "WHERE (description = '" & strn & "'))) " _
        & "OR (instrument_id IN " _
        & "    (SELECT sub_category_of " _
        & "     FROM instruments AS instruments_1 " _
        & "     WHERE (description = '" & strn & "'))) " _
        & "OR (instrument_id IN " _
        & "    (SELECT meta_instrument " _
        & "     FROM instruments AS instruments_1 " _
        & "     WHERE (description = '" & strn & "')))"

The actual query that gets executed against sql server:

SELECT instrument_id, description, sub_category_of, meta_instrument _
FROM instrument_ref 
    WHERE  (instrument_id IN 
(SELECT instrument_id
    FROM instruments
    WHERE (description = 'Women's Choir'))) 
    OR (instrument_id IN 
        (SELECT sub_category_of
            FROM instruments AS instruments_1
            WHERE (description = 'Women's Choir')))
    OR (instrument_id IN
        (SELECT meta_instrument
            FROM instruments AS instruments_1
            WHERE (description = 'Women's Choir')))

I would like to ask for help in how the single quote can be handled so that this error can be corrected. Do I escape it in the 'strn' variable, or do I do it inside the inline query?

Thank you much.

Upvotes: 3

Views: 2043

Answers (1)

Black Light
Black Light

Reputation: 2404

As suggested, use parameters:

SQL = "SELECT instrument_id, description, sub_category_of, meta_instrument" _
& " FROM instruments " _
& " WHERE  (instrument_id IN " _
& " (SELECT instrument_id " _
    & "FROM instruments " _
    & "WHERE (description = @description))) " _
        & "OR (instrument_id IN " _
        & "    (SELECT sub_category_of " _
        & "     FROM instruments AS instruments_1 " ... etc

Then, when you construct the SqlCommand object with this SQL, use the command object's ".Parameters.AddWithValue()" method to add a parameter called "@description" and the appropriate value.

This, by the way, prevents someone from causing havoc by entering a description of "; drop table instruments;".

Upvotes: 3

Related Questions