SQL Injection vulnerability allows attackers to alter database queries to take actions other than what the developer intended. This could allow an attacker to bypass authentication, steal data, alter site and database contents, or even destroy your database.
There are many forms of SQL Injection, but let's look at a simple example to get around authentication.
Let’s say you have a SQL Query that is structured like this:
query = "SELECT * FROM users WHERE name='" + user + "' AND password='" + password + "'";
Look at what would happen if you used this for the password:
x' OR '1'='1
The resulting SQL Query would look like this:
SELECT * FROM users WHERE name='jane' AND password='x' OR '1'='1';
By using "Or one equals one", the WHERE clause will always evaluate to true, which would cause the application to believe you entered the correct password.
Let’s look at this with a real application. A password like the following is going to inject properly into the query and bypass the site’s authentication:
x' OR '1' = '1
Now that we’ve bypassed the authentication in the app, we see a social media feed. There may be more advanced SQL injection vulnerabilities in other parts of that application that can be explored.
To try this yourself and see how to exploit the rest of this application, which includes dumping the contents of the database and dropping tables, you can see the full lesson on our website.
Now that we can see the impact of a SQL Injection vulnerability, let’s look at the code running in this sandbox to see how to protect against attackers.
In this demonstration, let’s examine the code in Python.
As you see on line 9, string concatenation is being used to build the SQL Query. Prepared statements or parameterized queries should be used whenever your SQL Statements use a variable as input.
This separates both data and commands so that data won't be "executed". When used safely, stored procedures are similar to prepared statements in that there is a separation of data and commands.
Here is an example of a parameterized query in Python:
c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))
The parameters are passed as data and will not be executed as part of the SQL statement. Most programming languages have libraries available for prepared statements.
Lastly, regardless of whether prepared statements and stored procedures are available or not, sanitizing data should be used to remove special characters or statements. Relying solely on sanitization is a mistake, as special characters are database specific and sanitization is often implemented incorrectly, so it should not be used alone if other techniques are available.
You should also validate all input data on the server side, ideally with a whitelisted set of parameters. In the example from this exercise, the only input that should be accepted are usernames currently available in the database. If it is not feasible, usernames could only be alphanumeric. Always enforce least privilege, giving the least privilege necessary to complete a task, and ensure that the database connection is set at the lowest privilege necessary to execute the query.