How can I work with quotes in SQL?

Question

I have a database with names in it such as John Doe, etc. Unfortunately, some of these names contain quotes such as Keiran O'Keefe. Now, when I try to find such names, follow these steps:

SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe' 

I (understandably) get an error message.

How to prevent this error. I use Oracle and PLSQL.

Answer 1

The equivalent character is', so you will need to replace the quote with two quotation marks.

For example,

SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe'

becomes

SELECT * FROM PEOPLE WHERE SURNAME='O''Keefe'

However, it is probably wrong to do it yourself. Your language may have a function to avoid strings for use in SQL, but it is even better to use parameters. This usually works as follows.

Your SQL team will:

SELECT * FROM PEOPLE WHERE SURNAME=?

Then, when you execute it, you enter the O'Keefe parameter as the parameter.

Since SQL is parsed before the parameter value is set, there is no way to change the parameter value in the SQL structure (and this is even a little faster if you want to run the same statement several times with different parameters).

I should also note that although your example just causes an error, you open yourself up to many other problems without avoiding the relevant lines. See http://en.wikipedia.org/wiki/SQL_injection for a good starting point or the next classic xkcd comic .

Answer 2

Oracle 10 solution

 SELECT * FROM PEOPLE WHERE SURNAME=q'{O'Keefe}' 

Answer 3

I suppose the good question is which language do you use?
In PHP you would do: SELECT * FROM PEOPLE WHERE SURNAME = 'mysql_escape_string (O'Keefe)'
But since you did not specify the language, I suggest you take a look at the mysql escape function or otherwise in your language.

Answer 4

Parameterized queries are your friend, as Matt suggests.

 Command = SELECT * FROM PEOPLE WHERE SURNAME=? 

They will protect you from headaches associated with

• Quotation marks
• Date Query
• SQL injection

Answer 5

Using parameterized SQL has other advantages, it reduces the processor overhead (as well as other resources) in Oracle by reducing the amount of Oracle work required to analyze the statement. If you do not use parameters (we call them binding variables in Oracle), then "select * from foo, where bar = 'cat'" and "select * from foo, where bar = 'dog'" are treated as separate instructions, select * from foo, where bar =: b1 "is the same operator, which means such things as syntax, the reliability of the objects referenced, etc. .... you do not need to check again. There are random problems that arise when using variables bindings, which usually occur in that they don’t get the most efficient SQL execution plan, but there are some ways to do this nye way, and these problems are really dependent on the predicate that you use, indexing and data corruption.

Answer 6

Input filtering is usually done at the language level, and not in the database layers.
php and .NET have their respective libraries for escaping sql statements. Check your language, see Waht.
If your data is reliable, you can simply replace the string to add another “infront of” to avoid it. This is usually sufficient if there is no danger that the input is harmful.

Answer 7

To make deals, if you are using the Zend Framework, here is the code

$db = Zend_Db_Table_Abstract::getDefaultAdapter();

$db->quoteInto('your_query_here = ?','your_value_here');

eg:

 //SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe' will become SELECT * FROM PEOPLE WHERE SURNAME='\'O\'Keefe\'' 

Answer 8

Found less than 30 years on Google ...

Oracle SQL Frequently Asked Questions