在Swift中，使用带撇号的SQL查询。

Question

在Swift中，使用带撇号的SQL查询。

3

我有一个用户正在破坏我的代码，我认为这是因为他的名字里有一个撇号。我已经尝试使用双撇号和反斜杠进行转义，但我一直收到“fatal error: unexpectedly found nil while unwrapping an Optional value”错误提示。

    let querySQL = "SELECT name from member_data ORDER BY RANDOM() LIMIT 1" 
    rightAnswer = memberDatabase!.executeQuery(querySQL, withArgumentsInArray: nil)
    rightAnswer!.next()
    correctName = rightAnswer!.stringForColumn("name")!
    println("Correct answer is \(correctName)") 
      //prints as Optional("Sam O\'Neal")
    let sanitizedName = correctName?.stringByReplacingOccurrencesOfString("'", withString: "\'")   
      //tried many combinations of this
    let wrongAnswerSQLQuery = "SELECT name from member_data where name is not '\(sanitizedName)' LIMIT 3"
    let wrongAnswersResultSet:FMResultSet = memberDatabase!.executeQuery(wrongAnswerSQLQuery, withArgumentsInArray: nil)

- thumbtackthief

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rob · Accepted Answer

有三个问题：

You are using C-style escapes in your SQL. The SQLite documentation says

A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.

So replace ' characters with '':
```
let sanitizedName = correctName?.stringByReplacingOccurrencesOfString("'", withString: "''")
```

Your sanitizedName is optional, so you have to unwrap it or else it will appear as Optional(...):

let wrongAnswerSQLQuery = "SELECT name from member_data where name is not '\(sanitizedName!)' LIMIT 3"

Even better, you should cut the Gordian knot, and not build SQL statement with string values like this at all.

Instead, you should use the ? placeholders in your SQL and then pass the correctName in the withArgumentsInArray. Then you don't have to worry about doing any of this "sanitizing" at all:
```
let wrongAnswerSQLQuery = "SELECT name from member_data where name is not ? LIMIT 3"
let wrongAnswersResultSet = memberDatabase!.executeQuery(wrongAnswerSQLQuery, withArgumentsInArray: [correctName!])
```