What does a question mark represent in SQL queries?
SqlPrepared StatementSql Problem Overview
While going through some SQL books I found that examples tend to use question marks (?
) in their queries. What does it represent?
Sql Solutions
Solution 1 - 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()
You write this:
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; --
(Answer is here)
Write this instead:
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; --'"
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()
Solution 2 - Sql
The ?
is an unnamed parameter which can be filled in by a program running the query to avoid SQL injection.
Solution 3 - Sql
The ?
is to allow Parameterized Query. These parameterized query is to allow type-specific value when replacing the ?
with their respective value.
That's all to it.
Here's a reason of why it's better to use Parameterized Query. Basically, it's easier to read and debug.
Solution 4 - Sql
It's a parameter. You can specify it when executing query.
Solution 5 - Sql
I don't think that has any meaning in SQL. You might be looking at Prepared Statements in JDBC or something. In that case, the question marks are placeholders for parameters to the statement.
Solution 6 - Sql
It normally represents a parameter to be supplied by client.