How Do Parameterized Queries with Question Marks Prevent SQL Injection Attacks?

Preventing SQL Injection with Parameterized Queries: The Question Mark Approach

Parameterized queries, indicated by the question mark (?) in SQL, are a crucial defense against SQL injection vulnerabilities. These queries are essential when constructing and executing SQL statements dynamically within a program.

The core advantage of parameterized queries over manually constructed queries lies in enhanced security. The database library handles parameter insertion, automatically applying necessary escaping mechanisms to prevent malicious code injection. Consider this vulnerable example:

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

If a user inputs: Robert'); DROP TABLE students; --

The resulting query becomes susceptible to attack.

The solution involves using parameterized queries:

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

The library now sanitizes the input, rendering the malicious code harmless: The query effectively becomes "SELECT * FROM students WHERE name = 'Robert''); DROP TABLE students; --'" — the injected code is treated as literal text.

While the question mark is a widely used placeholder, other database systems, such as MS SQL Server, utilize named parameters (e.g., @varname) for improved readability:

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

This approach offers the same security benefits with clearer parameter identification.

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