SQL Server: apostrophes in queries

Tweet

Apostrophes ( ' ) in queries might be a big problem. In fact, we usually build our queries without thinking about it, maybe because it's quite unusual to have data containing such strange characters. That leads to difficulties in isolating the cause of an error while executing a query statement, especially when the query worked for a year and then - suddenly - starts giving problems. It is quite common not to think about new data recently entered or updated, and usually we concentrate on the query statement and thus - let me say - waste a lot of time/money.
An example
Let me explain how that happens in a very simple example. Your query is something like:

query = "SELECT * FROM people WHERE lsname like '" & last_name &"' "

As you can see, we filter the table (people) using the lsname and passing a variable called last_name.
Now, imagine this:

last_name = "O'Neill"

As you can see the name contains an apostrophe. That means your query will be executed as:

SELECT * FROM people WHERE lsname like 'O'Neill'

causing an unwanted end to the statement after the O, and leaving the rest of the statement suspended.

The solution

To solve the issue, we can use the REPLACE command.

last_name = REPLACE ( last_name, "'", "''")

The above snippet will replace any apostrophe with a double apostrophe. That will make your query like:

SELECT * FROM people WHERE lsname like 'O''Neill'

making your statement correct.
Use REPLACE every time you're dealing with query's variables. You can even think of creating a function to be called before passing parameters.
One last thing: remember that the apostrophe can be use to exploit your code, so you really need to deal with it. Always.