SQL Parameterized Query and Question Mark

When looking up SQL documents, you may encounter a question mark (?) in your query. These placeholders represent parameterized queries and are widely used to execute dynamic SQL in programs.

Parameterized query has many advantages. They simplify code by separating parameter values ​​from the query itself, making it more efficient and flexible. Additionally, they enhance security by preventing SQL injection attacks.

For example, in a pseudo-code example:

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

can be rewritten as:

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

This technique ensures correct string escapes, eliminating the risk of SQL injection. Consider the following scenario:

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

If the user enters the string Robert'); DROP TABLE students; --, a SQL injection attack may occur. However, use parameterized query:

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

Library functions clean up the input to prevent malicious code execution.

Or, Microsoft SQL Server uses named parameters, which improves readability and clarity:

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