如何处理SQL中的引号' [英] How do I deal with quotes ' in SQL

问题描述

我有一个数据库，其中有一些名称，例如John Doe等.不幸的是，其中一些名称包含像Keiran O'Keefe这样的引号.现在，当我尝试搜索如下名称时:

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:

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

我(很容易理解)得到一个错误.

I (understandably) get an error.

如何防止发生此错误.我正在使用Oracle和PLSQL.

How do I prevent this error from occurring. I am using Oracle and PLSQL.

推荐答案

转义字符为'，因此您需要将引号替换为两个引号.

The escape character is ', so you would need to replace the quote with two quotes.

例如，

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

成为

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

也就是说，自己做这可能是不正确的.您的语言可能具有将字符串转义以用于SQL的功能，但是更好的选择是使用参数.通常，它的工作原理如下.

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命令为:

SELECT * FROM PEOPLE WHERE SURNAME=?

然后，在执行它时，将"O'Keefe"作为参数传递.

Then, when you execute it, you pass in "O'Keefe" as a parameter.

因为在设置参数值之前已对SQL进行了解析，所以参数值无法更改SQL的结构(如果要使用不同的参数多次运行同一条语句，则速度会更快一些)

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).

我还应该指出，尽管您的示例仅会导致错误，但是由于没有适当地转义字符串，您使自己面临许多其他问题.请参阅 http://en.wikipedia.org/wiki/SQL_injection 以获得一个好的起点或以下经典 xkcd漫画.

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_injection for a good starting point or the following classic xkcd comic.

这篇关于如何处理SQL中的引号'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！