How Do Parameterized Queries in SQL Prevent SQL Injection Attacks?

SQL parameterized query with question mark

When consulting the SQL documentation, you may encounter question marks (?) in queries. These placeholders represent parameterized queries and are widely used to execute dynamic SQL in programs.

Parameterized queries have many advantages. They simplify code by decoupling 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 pseudocode example:

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

can be rewritten as:

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

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

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

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

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

The library function will clean the input to prevent malicious code execution.

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

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

The above is the detailed content of How Do Parameterized Queries in SQL Prevent SQL Injection Attacks?. For more information, please follow other related articles on the PHP Chinese website!