Best Practices for Preventing SQL Injection Attacks

This is the 5th post in a series on MySQL performance.

Don’t let users input code, but let them input values.

Web developers see a lot of things threatening to harm the applications they build. One such attack targets your database and the data stored there — SQL injection. A common purpose of such an attack is to exfiltrate sensitive data from the database for personal gain. Developers overlook the importance of security when creating public, web-facing solutions, which is why this attack is so prevalent and dangerous.

What is SQL Injection?

An SQL injection attack involves injecting SQL queries into the application via input data from the client. SQL injection exploits can read sensitive database data, modify database data (Insert/Update/Delete), perform administration operations on the database (such as shutdown the DBMS), and in some cases issue commands to the operating system. In SQL injection attacks, SQL commands are injected into data-plane input in order to execute predefined SQL commands.

How Does SQL Injection Work?

A SQL Injection flaw occurs when software developers construct dynamic database queries based on string concatenation that includes user input. An SQL injection attack basically works by putting everything the user writes into an input field into a query and passing it to a database.

SQL injection flaws can be avoided easily. There are two options for developers:

• Stop writing dynamic queries that concatenate strings; and/or
• Ensure that user-supplied input containing malicious SQL does not affect the query logic.

Antipattern: Execute Unverified Input As Code

The SQL injection occurs when you interpolate some content into an SQL query string and it modifies the syntax of your query in ways you didn’t intend. As with the classic example of SQL Injection, the value interpolated into your string completes the SQL statement and executes a second complete statement.

As an example, if ‘bugId’ is 1234; DELETE FROM bugs, the resulting SQL would be as follows:

SELECT * FROM bugs WHERE bug_id = 1234; DELETE FROM bugs

All bugs would be deleted from the bugs table.

When an attacker can manipulate your SQL statements, SQL Injection becomes more dangerous. Your application may allow users to change their passwords, for instance.

Your SQL statement can be exploited by a clever attacker who knows how the request parameters are used.

UPDATE accounts SET password_hash = SHA2('securepassword') WHERE account_id = 123 or TRUE;

After interpolating the string from the ‘userId’ parameter into your SQL expression, the string has changed the syntax. It now changes passwords for every account in the database, not just one.

By changing the syntax of the SQL statement before it is parsed, SQL Injection works. You run the risk of SQL Injection if you insert dynamic portions before the statement is parsed.

Primary Defenses

* Option 1: Prepared Statements (With Parameterized Queries)

You should leave parameter placeholders in your SQL string rather than interpolating dynamic values. As you execute the prepared query, provide a parameter value.

* Option 2: Stored Procedures

A stored procedure is another method that many software developers claim is proof against SQL Injection vulnerabilities. Stored procedures typically contain fixed SQL statements, parsed when they are defined. Dynamic SQL can, however, be used in stored procedures in an unsafe manner. Dynamic SQL in stored procedures is as safe as dynamic SQL in application code.

* Option 3: Allow-list Input Validation

It’s not legal to use bind variables in certain parts of SQL queries, such as the names of tables and columns and sort order indicators (ASC or DESC). The best defense in such cases is input validation or query redesign.

* Option 4: Escaping All User Supplied Input

SQL queries can be protected from accidental unmatched quote characters by escaping any quote characters to prevent them from becoming the end of the quoted string. Standard SQL allows you to make a literal quote character by using two quote characters.

SELECT * from department where department_name='O''mega';

The following quote character can also be escaped with a backslash in most databases.

SELECT * from department where department_name='O\'mega';

Working principles to prevent SQL Injections

Almost all database applications build SQL statements dynamically. Concatenating strings together or interpolating variables into strings can expose your application to SQL Injection attacks if you build any portion of an SQL statement in this manner.

You cannot secure your SQL code with a single technique. All of the above techniques should be learned and used appropriately.

Guidelines for code review for SQL Injection

• Find SQL statements formed using application variables, string concatenation, or replacements.
• All dynamic content used in your SQL statements should be traced back to its source. Look for data that originates from an external source, including user input, files, the environment, web services, third-party code, or even a database string.
• Any external content should be regarded as potentially hazardous. Filters, validators, and mapping arrays can be used to transform untrusted content.
• Utilize robust escaping functions or query parameters to integrate external data into your SQL statements.
• Check your stored procedures and other places where dynamic SQL statements may be found.