How Do Prepared Statements Prevent SQL Injection?

Prepared Statements: A Robust Defense Against SQL Injection (Part 2)

Prepared statements offer a proactive approach to SQL injection prevention. They achieve this by separating user-supplied data from the SQL query structure. Instead of embedding user input directly into the query string, prepared statements use parameters, typically represented as question marks (?), as placeholders.

The database engine parses and compiles the prepared statement before the user data is added. This crucial step ensures that the user input is treated solely as data, preventing its interpretation as executable SQL code. This contrasts sharply with the vulnerable method of directly concatenating user input into the SQL string.

Illustrative Example: Consider inserting user data into a database.

Vulnerable String Concatenation:

<code class="language-java">PreparedStatement stmt = conn.createStatement("INSERT INTO students VALUES('" + user + "')");
stmt.execute();</code>

Secure Prepared Statement:

<code class="language-java">PreparedStatement stmt = conn.prepareStatement("INSERT INTO student VALUES(?)");
stmt.setString(1, user);
stmt.execute();</code>

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

The string concatenation method results in:

<code class="language-sql">INSERT INTO students VALUES('Robert'); DROP TABLE students; --')</code>

This executes the malicious DROP TABLE command.

However, with the prepared statement, the database executes:

<code class="language-sql">INSERT INTO student VALUES('Robert');</code>

The malicious input is treated as literal data, neutralizing the SQL injection threat. Prepared statements effectively isolate the query's logic from potentially harmful user input, thus guaranteeing the integrity of the SQL command.

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