Which of the following can be used as a countermeasure against the SQL injection attack? Each correct answer represents a complete solution. Choose…

Prepared statements (also known as parameterized queries) are the most effective and recommended countermeasure against SQL injection. This method separates the SQL logic from the data. The database engine is sent the query template first and then the user-supplied parameters, ensuring the parameters are treated strictly as data and not as executable SQL code.

The mysqlrealescapestring() function is a valid, though now deprecated, defense mechanism. It works by escaping special characters (e.g., \x00, \n, \r, ', ", \x1a) within user-provided strings before the string is used in a SQL query, preventing them from being misinterpreted by the database as control characters or delimiters.

C. mysqlescapestring() is a deprecated and insecure function because it does not account for the connection's character set, making it vulnerable to specific injection techniques.

D. sessionregenerateid() is a security measure used to prevent session fixation attacks by replacing the current session ID with a new one; it is unrelated to SQL injection.

1. PHP Documentation

Prepared Statements (PDO): The official PHP manual states

"The most significant benefit of prepared statements is that they protect against SQL injection... By using placeholders

the database can distinguish between the query logic and the data

preventing malicious input from altering the intended SQL command." (Source: PHP Manual

PDO::prepare function documentation).

2. PHP Documentation

mysqlrealescapestring(): "Escapes special characters in a string for use in an SQL statement... This function must always... be used to make data safe before sending a query to MySQL." The documentation also notes that the mysql extension itself is deprecated and recommends using MySQLi or PDO with prepared statements. (Source: PHP Manual

mysqlrealescapestring function documentation).

3. Stanford University

CS 155: Computer and Network Security

Lecture 10: Web Security - Attacks: Course materials identify two primary defenses against SQL injection: "1. Prepared statements (best method)" and "2. Escaping user input." The lecture explicitly contrasts the insecure mysqlescapestring() with the more secure mysqlrealescapestring(). (Source: Stanford University

CS 155 Course Materials

Fall 2018

Slide 45-48).