Skip to content

3. SQL injection prevention

SQL injection is one of the most common and dangerous security vulnerabilities in web applications, where malicious SQL code is inserted into a query to manipulate the database. To prevent SQL injection, you need to follow best practices that ensure safe interaction with your database.

Best Practices for Preventing SQL Injection:

  1. Use Prepared Statements (Parameterized Queries):
  2. Prepared statements ensure that SQL queries are executed with the parameters treated as data, not executable code. This effectively separates the query structure from user input, preventing malicious input from altering the query.

Example: - In Python (with psycopg2 for PostgreSQL): 

cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))

  • In PHP (with PDO): 

    $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
$stmt->execute(['username' => $username, 'password' => $password]);

  • Use Stored Procedures (when applicable):

  • Stored procedures encapsulate SQL code within the database, which can help prevent SQL injection. However, be cautious—if a stored procedure still concatenates user inputs within queries, it may still be vulnerable to injection.

Example: - In SQL Server or MySQL: 

CREATE PROCEDURE GetUser(@username NVARCHAR(50), @password NVARCHAR(50))
AS
BEGIN
    SELECT * FROM users WHERE username = @username AND password = @password;
END

  1. Use ORM (Object-Relational Mapping) Frameworks:
  2. Many modern web frameworks and libraries (such as Django ORM, SQLAlchemy, or Hibernate) automatically protect against SQL injection by using parameterized queries or query-building functions.

Example in Django: 

user = User.objects.get(username=username, password=password)

  1. Input Validation and Sanitization:
  2. Whitelist acceptable input types. For example, if you expect a numeric ID, only allow numeric input and reject non-numeric values.
  3. Sanitize input to remove or escape dangerous characters (like ', ", --, ;).

Example (basic sanitization): - Use built-in functions to escape special characters. - Validate email addresses, phone numbers, etc., using regex.

  1. Least Privilege Principle:

  2. Ensure that the database account used by your application has the least privilege required. This limits the potential impact of a successful SQL injection attack. For example, if your application doesn’t need to delete records, don’t grant it DELETE privileges.

  3. Error Handling:

  4. Avoid exposing database errors to users. Detailed error messages can provide attackers with information about the database structure and potential vulnerabilities.

  5. Implement generic error messages and log full errors on the server-side.

  6. Use Web Application Firewalls (WAF):

  7. A Web Application Firewall can help detect and block SQL injection attempts based on known attack patterns.

  8. Avoid Dynamic SQL Queries:

  9. Avoid building SQL queries dynamically by concatenating user input into the query string. This is one of the major causes of SQL injection vulnerabilities.

Example of bad practice: 

query = "SELECT * FROM users WHERE username = '" + user_input + "'"
cursor.execute(query)

  1. Regular Security Audits and Penetration Testing:
  2. Continuously test your application for SQL injection vulnerabilities and ensure all user inputs are handled properly.
  3. Use tools like OWASP ZAP, SQLmap, or Burp Suite to automate security testing and identify weaknesses.

By adhering to these practices, you can effectively protect your SQL-based systems from SQL injection vulnerabilities, significantly improving the security of your applications.