MySQL Injection: Query Security

TL;DR

These queries are likely vulnerable to MySQL injection if user input isn’t properly sanitised. This guide shows you how to fix it using prepared statements and escaping, with examples.

Understanding the Risk

MySQL injection happens when malicious code is inserted into a query through user-supplied data. This can allow attackers to read, modify or delete your database information. It’s a serious cyber security threat.

Fixing Vulnerable Queries

1. Identify User Input: First, pinpoint all places where you’re taking input from users (forms, URLs, APIs etc.). Any data coming directly or indirectly from the user is potential injection risk.
2. Prepared Statements (Recommended): Prepared statements separate the query structure from the data. This prevents the database from interpreting user input as part of the SQL command itself. Most modern programming languages have libraries to support this.
   • PHP Example:

     prepare("SELECT * FROM users WHERE username = ? AND password = ?");
     $stmt->execute([$_POST['username'], $_POST['password']]);
     $user = $stmt->fetch();
     ?>

   • Python Example (using mysql.connector):

     import mysql.connector
     mysql = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
     cursor = mysql.cursor()
     sql = "SELECT * FROM users WHERE username = %s AND password = %s"
     val = (user_input_username, user_input_password)
     cursor.execute(sql, val)
     myresult = cursor.fetchall()

3. Escaping User Input (If Prepared Statements Aren’t Possible): If you absolutely can’t use prepared statements (which is rare these days), escape special characters in the user input before including it in your query. This makes sure they are treated as data, not SQL commands.
   • PHP Example:

     Important: This is less secure than prepared statements. Use it only as a last resort.

   • Python Example (using mysql.connector):

     import mysql.connector
     mysql = mysql.connector.connect(host="localhost", user="your_username", password="your_password", database="your_database")
     cursor = mysql.cursor()
     escaped_username = cursor.escape(user_input_username)
     sql = "SELECT * FROM users WHERE username = '" + escaped_username + "' AND password = '" + cursor.escape(user_input_password) + "'";
     cursor.execute(sql)

     Important: This is less secure than prepared statements and should be avoided if possible.

4. Input Validation: Before even escaping or preparing, validate the input to ensure it matches expected formats. For example:
   • Check data types (is it a number when you expect a number?).
   • Limit string lengths.
   • Use whitelisting – only allow known good characters.
5. Least Privilege: Ensure your database user has the minimum necessary permissions. Don't give it full access if it only needs to read certain tables.
6. Regular Security Audits: Regularly review your code and database configuration for potential vulnerabilities. Consider using automated cyber security scanning tools.

$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = ''