<p>I have a database with names in it such as John Doe etc. Unfortunately some of these names contain quotes like  Keiran O'Keefe. Now when I try and search for such names as follows:</p>

<pre><code>SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe' 
</code></pre>

<p>I (understandably) get an error.</p>

<p>How do I prevent this error from occurring. I am using Oracle and PLSQL.</p>

[{'answer_id': 29703, 'author': 'Rob Cooper', 'author_id': 832, 'author_profile': 'https://Stackoverflow.com/users/832', 'pm_score': -1, 'selected': False, 'text': '<p>Found in under 30s on Google...</p>\n\n<p><a href="http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_escape_special_characters_when_writing_SQL_queries.3F" rel="nofollow noreferrer">Oracle SQL FAQ</a></p>\n'}, {'answer_id': 29707, 'author': 'paan', 'author_id': 2976, 'author_profile': 'https://Stackoverflow.com/users/2976', 'pm_score': 0, 'selected': False, 'text': "<p>Input filtering is usually done on the language level rather than database layers.<br>\nphp and .NET both have their respective libraries for escaping sql statements. Check your language, see waht's available.<br>\nIf your data are trustable, then you can just do a string replace to add another ' infront of the ' to escape it. Usually that is enough if there isn't any risks that the input is malicious.</p>\n"}, {'answer_id': 29708, 'author': 'UnkwnTech', 'author_id': 115, 'author_profile': 'https://Stackoverflow.com/users/115', 'pm_score': 0, 'selected': False, 'text': "<p>I suppose a good question is what language are you using?<br />\nIn PHP you would do: SELECT * FROM PEOPLE WHERE SURNAME='mysql_escape_string(O'Keefe)'<br />\nBut since you didn't specify the language I will suggest that you look into a escape string function mysql or otherwise in your language.</p>\n"}, {'answer_id': 29727, 'author': 'Matt Sheppard', 'author_id': 797, 'author_profile': 'https://Stackoverflow.com/users/797', 'pm_score': 6, 'selected': True, 'text': '<p>The escape character is \', so you would need to replace the quote with two quotes.</p>\n\n<p>For example,</p>\n\n<p><code>SELECT * FROM PEOPLE WHERE SURNAME=\'O\'Keefe\'</code></p>\n\n<p>becomes</p>\n\n<p><code>SELECT * FROM PEOPLE WHERE SURNAME=\'O\'\'Keefe\'</code></p>\n\n<p>That said, it\'s probably incorrect to do this yourself. Your language may have a function to escape strings for use in SQL, but an even better option is to use parameters. Usually this works as follows.</p>\n\n<p>Your SQL command would be : </p>\n\n<p><code>SELECT * FROM PEOPLE WHERE SURNAME=?</code></p>\n\n<p>Then, when you execute it, you pass in "O\'Keefe" as a parameter.</p>\n\n<p>Because the SQL is parsed before the parameter value is set, there\'s no way for the parameter value to alter the structure of the SQL (and it\'s even a little faster if you want to run the same statement several times with different parameters).</p>\n\n<p>I should also point out that, while your example just causes an error, you open youself up to a lot of other problems by not escaping strings appropriately. See <a href="http://en.wikipedia.org/wiki/SQL_injection" rel="nofollow noreferrer">http://en.wikipedia.org/wiki/SQL_injection</a> for a good starting point or the following classic <a href="http://xkcd.com/327/" rel="nofollow noreferrer">xkcd comic</a>.</p>\n\n<p><img src="https://imgs.xkcd.com/comics/exploits_of_a_mom.png" alt="alt text"></p>\n'}, {'answer_id': 30303, 'author': 'Rad', 'author_id': 1349, 'author_profile': 'https://Stackoverflow.com/users/1349', 'pm_score': 1, 'selected': False, 'text': '<p>Parameterized queries are your friend, as suggested by Matt. </p>\n\n<pre><code>Command = SELECT * FROM PEOPLE WHERE SURNAME=?\n</code></pre>\n\n<p>They will protect you from headaches involved with</p>\n\n<ul>\n<li>Strings with quotes</li>\n<li>Querying using dates</li>\n<li>SQL Injection</li>\n</ul>\n'}, {'answer_id': 44074, 'author': 'Ethan Post', 'author_id': 4527, 'author_profile': 'https://Stackoverflow.com/users/4527', 'pm_score': 1, 'selected': False, 'text': '<p>Use of parameterized SQL has other benefits, it reduces CPU overhead (as well as other resources) in Oracle by reducing the amount of work Oracle requires in order to parse the statement. If you do not use parameters (we call them bind variables in Oracle) then "select * from foo where bar=\'cat\'" and "select * from foo where bar=\'dog\'" are treated as separate statements, where as "select * from foo where bar=:b1" is the same statement, meaning things like syntax, validity of objects that are referenced etc...do not need to be checked again. There are occasional problems that arise when using bind variables which usually manifests itself in not getting the most efficient SQL execution plan but there are workarounds for this and these problems really depend on the predicates you are using, indexing and data skew.</p>\n'}, {'answer_id': 84265, 'author': 'Laurent Schneider', 'author_id': 16166, 'author_profile': 'https://Stackoverflow.com/users/16166', 'pm_score': 2, 'selected': False, 'text': "<p>Oracle 10 solution is</p>\n\n<pre><code>SELECT * FROM PEOPLE WHERE SURNAME=q'{O'Keefe}'\n</code></pre>\n"}, {'answer_id': 9307168, 'author': 'rjmcb', 'author_id': 1166176, 'author_profile': 'https://Stackoverflow.com/users/1166176', 'pm_score': 0, 'selected': False, 'text': "<p>To deal quotes if you're using Zend Framework here is the code </p>\n\n<p><code>$db = Zend_Db_Table_Abstract::getDefaultAdapter();</code></p>\n\n<p><code>$db-&gt;quoteInto('your_query_here = ?','your_value_here');</code></p>\n\n<p>for example ;</p>\n\n<pre><code>//SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe' will become\nSELECT * FROM PEOPLE WHERE SURNAME='\\'O\\'Keefe\\''\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/29699', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/445/']