Codecademy Logo

Identify & Prevent SQL Injection Attacks

Mitigating SQL Injection Attacks: Input Sanitization

One way SQL injections can be mitigated is through input sanitization. Sanitization is the process of removing dangerous characters from user input.

Dangerous characters might include:

  • ;
  • \--

This is important because they allow attackers to extend SQL queries to gain more information from a database.

Careful, this method is not the perfect defense against SQL injections. Removing characters may have no effect in some queries and, if an attacker finds a way to bypass the sanitization process, they can easily inject data into your system.

SELECT username, email FROM users WHERE id = '1' AND '1' = '2';

Mitigating SQL Injection Attacks: Prepared Statements

One way SQL injections can be mitigated is through prepared statements. With prepared statements, the query we want to execute is provided to the database in advance. Any input is then treated as a parameter and will not be treated as SQL code.

  1. First, a SQL query template is sent to the database. Certain values, called parameters, are left unspecified. For example, user input.
  2. The database processes the query and performs optimizations.
  3. Values are bound to the parameters and the SQL query is executed.

This method is a nearly foolproof and reliable solution to SQL injections.

$username= $_GET['user']; // Set parameter
$stmt = $conn->prepare("SELECT * FROM Users WHERE name = '?'"); // Prepare statement
$stmt->bind_param("s", $username); // Bind parameter to SQL query
$stmt->execute(); // Execute the SQL query

Mitigating SQL Injection Attacks: validator.js

The npm package, validator.js, is a library of string validators and sanitizers that includes functions to clean up data inputs, which helps mitigate SQL injections.

Some of the input validators include:

  • isEmail() - Checks if the input is a valid email address
  • isLength() - Checks if the input is a certain length
  • isCurrency() - Checks if the input is currency-formatted
  • isMobilePhone() - Checks is the input is a valid mobile phone number

Some of the sanitizers include:

  • normalizeEmail() - Removes formatting on email inputs to remove potentially dangerous characters
  • escape() - Replaces <, >, &, ', and " characters that could be confused with HTML entities
// Prints "true"
// Prints "[email protected]"
console.log(validator.normalizeEmail(" [email protected]"))
// Prints "1 &lt; 2"
console.log(validator.escape("1 < 2"))