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.
Let me explain how that happens in a very simple example. Your query is something like:
As you can see, we filter the table (people) using the lsname and passing a variable called last_name.
query = "SELECT * FROM people WHERE lsname like '" & last_name &"' "
Now, imagine this:
As you can see the name contains an apostrophe. That means your query will be executed as:
last_name = "O'Neill"
causing an unwanted end to the statement after the O, and leaving the rest of the statement suspended.
SELECT * FROM people WHERE lsname like 'O'Neill'
The solutionTo solve the issue, we can use the REPLACE command.
The above snippet will replace any apostrophe with a double apostrophe. That will make your query like:
last_name = REPLACE ( last_name, "'", "''")
making your statement correct.
SELECT * FROM people WHERE lsname like 'O''Neill'
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.