SQL Injection Protection: Fallacy of Single Quotes Escape

In the field of software development, preventing SQL injection attacks is crucial. While parameterized SQL queries are the best way to clean up inputs, some developers still use the defense mechanism that escapes single quotes and encloses user inputs in single quotes as an alternative.

Flawed escape technique

The method involves replacing any single quotes in user input with double single quotes and enclosing the entire string in single quotes:

sSanitizedInput = "'" & Replace(sInput, "'", "''") & "'"

The principle of this technique is that any single quotes entered by the user are effectively neutralized to prevent the string from termination. Therefore, any other character, such as a semicolon or a percent sign, becomes part of the string and is not executed as a command.

Injection vulnerability

However, this technique cannot handle cases where user input itself may contain double single quotes. In this case, the string will terminate and the rest of the input can be executed as SQL commands.

Example input

To illustrate this, consider the following user input:

'SensitiveData' HAVING AMOUNT>2000 OR ''=''

After execution, the code will become:

SELECT * FROM ACCOUNT WHERE NAME='SensitiveData' HAVING AMOUNT>2000 OR ''=''

This input successfully injects the OR clause into the SQL query, bypassing the expected cleanup.

Further considerations

It is important to note that there are other vulnerabilities in this escape technique, including:

• Can't defend against all types of SQL injection attacks, such as attacks using comments or other statement terminators.
• Introduce performance and maintenance overhead.
• Make the code difficult to read and understand.

Best Practices

Do not rely on temporary input cleaning techniques, follow these best practices to prevent SQL injection:

• Use parameterized SQL query or JDBC pre-preparation statements.
• Only expected input values ​​and formats (whitelists) are allowed.
• Blacklists are only used if absolutely necessary and after other mitigation measures are implemented.
• Avoid dynamic SQL and string concatenation.
• Consider using stored procedures with limited database permissions.