What does a question mark represent in SQL queries?
Question: While going through some SQL books I found that examples tend to use question marks (
?) in their queries. What does it represent?
Answer: 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()