如何处理 SQL 中的引号 '
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29699/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I deal with quotes ' in SQL
提问by Calanus
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:
我有一个数据库，里面有名字，比如 John Doe 等。不幸的是，其中一些名字包含像 Keiran O'Keefe 这样的引号。现在，当我尝试搜索以下名称时：
SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe'
I (understandably) get an error.
我（可以理解）得到一个错误。
How do I prevent this error from occurring. I am using Oracle and PLSQL.
如何防止发生此错误。我正在使用 Oracle 和 PLSQL。
回答by Matt Sheppard
The escape character is ', so you would need to replace the quote with two quotes.
转义字符是 '，因此您需要用两个引号替换引号。
For example,
例如，
SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe'
SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe'
becomes
变成
SELECT * FROM PEOPLE WHERE SURNAME='O''Keefe'
SELECT * FROM PEOPLE WHERE SURNAME='O''Keefe'
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.
也就是说，自己这样做可能是不正确的。您的语言可能具有转义字符串以在 SQL 中使用的函数，但更好的选择是使用参数。通常这如下工作。
Your SQL command would be :
您的 SQL 命令将是：
SELECT * FROM PEOPLE WHERE SURNAME=?
SELECT * FROM PEOPLE WHERE SURNAME=?
Then, when you execute it, you pass in "O'Keefe" as a parameter.
然后，当您执行它时，您将“O'Keefe”作为参数传递。
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).
因为在设置参数值之前解析 SQL，参数值无法改变 SQL 的结构（如果你想用不同的参数多次运行相同的语句，它甚至会更快一点）。
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 http://en.wikipedia.org/wiki/SQL_injectionfor a good starting point or the following classic xkcd comic.
我还应该指出，虽然您的示例只会导致错误，但由于没有适当地转义字符串，您会遇到许多其他问题。请参阅http://en.wikipedia.org/wiki/SQL_injection以获得良好的起点或以下经典xkcd 漫画。
回答by Laurent Schneider
Oracle 10 solution is
Oracle 10 解决方案是
SELECT * FROM PEOPLE WHERE SURNAME=q'{O'Keefe}'
回答by Rad
Parameterized queries are your friend, as suggested by Matt.
正如 Matt 所建议的那样，参数化查询是您的朋友。
Command = SELECT * FROM PEOPLE WHERE SURNAME=?
They will protect you from headaches involved with
他们将保护您免于头痛
- Strings with quotes
- Querying using dates
- SQL Injection
- 带引号的字符串
- 使用日期查询
- SQL注入
回答by Ethan Post
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.
参数化 SQL 的使用还有其他好处，它通过减少 Oracle 解析语句所需的工作量来减少 Oracle 中的 CPU 开销（以及其他资源）。如果不使用参数（我们在 Oracle 中称它们为绑定变量），则“select * from foo where bar='cat'”和“select * from foo where bar='dog'”被视为单独的语句，其中作为“ select * from foo where bar=:b1" 是同一个语句，意思是语法、引用对象的有效性等……不需要再次检查。使用绑定变量时偶尔会出现一些问题，这通常表现为无法获得最有效的 SQL 执行计划，但有解决方法，这些问题实际上取决于您使用的谓词，
回答by rjmcb
To deal quotes if you're using Zend Framework here is the code
如果您使用 Zend Framework，要处理报价，这里是代码
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$db = Zend_Db_Table_Abstract::getDefaultAdapter();
$db->quoteInto('your_query_here = ?','your_value_here');
$db->quoteInto('your_query_here = ?','your_value_here');
for example ;
例如 ;
//SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe' will become
SELECT * FROM PEOPLE WHERE SURNAME='\'O\'Keefe\''
回答by paan
Input filtering is usually done on the language level rather than database layers.
php and .NET both have their respective libraries for escaping sql statements. Check your language, see waht's available.
If 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.
输入过滤通常在语言层而不是数据库层完成。
php 和 .NET 都有各自的用于转义 sql 语句的库。检查您的语言，看看有什么可用的。
如果您的数据是可信的，那么您只需进行字符串替换以在 ' 前面添加另一个 ' 以对其进行转义。如果没有任何输入是恶意的风险，通常这就足够了。
回答by UnkwnTech
I suppose a good question is what language are you using?
In PHP you would do: SELECT * FROM PEOPLE WHERE SURNAME='mysql_escape_string(O'Keefe)'
But since you didn't specify the language I will suggest that you look into a escape string function mysql or otherwise in your language.
我想一个很好的问题是你使用什么语言？
在 PHP 中你会这样做： SELECT * FROM PEOPLE WHERE SURNAME='mysql_escape_string(O'Keefe)'
但是由于你没有指定语言，我建议你查看转义字符串函数 mysql 或其他你的语言。
