SQL Injection? Reduce your attack surface!

Posted byjoshjlambertcoPosted inUncategorized

There are a few good ways to reduce the attack surface of SQL injection attacks, both in MySQL and more broadly in database security. 

First, input validation. Ensuring that an input matches an expected type or range of values, which can be done in code or with built-in filters, can help prevent the execution of malicious code through things like compromised URLs. It’s important not to trust client-side validation, since tools like cURL can bypass it. Server side validation should be used whenever possible.

Using precompiled SQL queries, like prepared statements, can also help. There are libraries that can be used which contain functions DBAs can use to keep functionality secure and consistent. Prepared statements generally require less overhead than the alternatives, and because they use parameterized values, they can use unescaped values and delimiters. Forcing the developer to prepare the SQL command separately from processing user data makes it challenging to stage a SQL injection attack.

for example, when user data is embedded directly in a SQL query:

$qry = “Select * From useraccounts Where user=’$usrname’ and password=’$pwd’;”

$proc = mysql_query($qry)

If a malicious user were to enter the username of a privileged account, they would be able to gain access without the need for the password. 

A prepared statement could be used to mitigate this risk:

$stmt = $mysqli->prepare(“Select * From useraccounts Where user= ? and password= ?;”

$stmt->bind_param(“ss”,$usrname,$pwd);

$stmt->execute();

Using placeholders in the prepared statement will prevent a string from being passed in and immediately executed. 

On the backend, prepared statements work by first parsing the query, checking semantics to establish validity of the query, then binding the query into machine-readable code. The DBMS then chooses a method for executing the query and caches it, to optimize future runs of the same query. Once that’s complete, the query is executed.

Broadly speaking, OWASP recommends the following techniques for avoiding SQL injection. 

Primary Defenses:

  • Option 1: Use of Prepared Statements (with Parameterized Queries)
  • Option 2: Use of Stored Procedures
  • Option 3: Allow-list Input Validation
  • Option 4: Escaping All User Supplied Input

Additional Defenses:

  • Also: Enforcing Least Privilege
  • Also: Performing Allow-list Input Validation as a Secondary Defense

They also provide language specific recommendations and further examples of safe and unsafe usage. 

Language specific recommendations:

  • Java EE – use PreparedStatement() with bind variables
  • .NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables
  • PHP – use PDO with strongly typed parameterized queries (using bindParam())
  • Hibernate – use createQuery() with bind variables (called named parameters in Hibernate)
  • SQLite – use sqlite3_prepare() to create a statement object (Links to an external site.)

Link for more information: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Leave a comment