问号在SQL查询中代表什么? [英] What does a question mark represent in SQL queries?

问题描述

在阅读一些SQL书籍时，我发现示例在查询中倾向于使用问号(?).它代表什么?

While going through some SQL books I found that examples tend to use question marks (?) in their queries. What does it represent?

推荐答案

您看到的是一个参数化查询.从程序执行动态SQL时经常使用它们.

What you are seeing is a parameterized query. They are frequently used when executing dynamic SQL from a program.

例如，与其写这个(注:伪代码):

For example, instead of writing this (note: pseudocode):

ODBCCommand cmd = new ODBCCommand("SELECT thingA FROM tableA WHERE thingB = 7")
result = cmd.Execute()

您这样写:

ODBCCommand cmd = new ODBCCommand("SELECT thingA FROM tableA WHERE thingB = ?")
cmd.Parameters.Add(7)
result = cmd.Execute()

这有很多优点，这很明显.最重要的功能之一:解析参数的库函数很聪明，并确保正确地转义了字符串.例如，如果您编写此代码:

This has many advantages, as is probably obvious. One of the most important: the library functions which parse your parameters are clever, and ensure that strings are escaped properly. For example, if you write this:

string s = getStudentName()
cmd.CommandText = "SELECT * FROM students WHERE (name = '" + s + "')"
cmd.Execute()

当用户输入此内容时会发生什么?

What happens when the user enters this?

Robert'); DROP TABLE students; --

(答案是此处)

改为写这个:

s = getStudentName()
cmd.CommandText = "SELECT * FROM students WHERE name = ?"
cmd.Parameters.Add(s)
cmd.Execute()

然后该库将对输入内容进行消毒，生成以下内容:

Then the library will sanitize the input, producing this:

"SELECT * FROM students where name = 'Robert''); DROP TABLE students; --'"

并非所有DBMS都使用?. MS SQL使用 named 参数，我认为这是巨大的改进:

Not all DBMS's use ?. MS SQL uses named parameters, which I consider a huge improvement:

cmd.Text = "SELECT thingA FROM tableA WHERE thingB = @varname"
cmd.Parameters.AddWithValue("@varname", 7)
result = cmd.Execute()

这篇关于问号在SQL查询中代表什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！