问号在SQL查询中代表什么?

本文介绍了问号在SQL查询中代表什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在阅读一些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()