How Parameterized Queries Stop SQL Injection Attacks

How parameterized queries work and stop SQL injection attacks. I will explain it clearly here, with examples.

What is SQL Injection?

Imagine you have a login form where users type in their username and password. A SQL query (a command that talks to the database) might look something like this:

sqlQuery = 'SELECT * FROM users WHERE username = ' + username + ' AND password = ' + password

In a normal situation, the query checks if the username and password entered by the user match any records in the database.

But what if someone tries to enter something dangerous, like this for the username:

' OR 1=1 --

The query would become:

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

What does this do?

• ‘ makes the username empty.
• OR 1=1 is always true (1 is always equal to 1).
• — is a comment in SQL, so everything after it is ignored.

This could trick the database into returning all the users in the table, or even log in as the first user, possibly an administrator. This is a SQL injection attack.

How Do Parameterized Queries Stop SQL Injection?

A parameterized query looks like this:

sqlQuery = 'SELECT * FROM users WHERE username = ? AND password = ?'

Instead of directly adding the username and password into the query, we use ? placeholders for them. Then, we use a function to bind the actual values to the placeholders:

parameters.add("username", username)

parameters.add("password", password)

Why Does This Work?

Now, you might think, “I could still try to inject SQL code into the username field, like ‘ OR 1=1 –.”

So, you might try this:

sqlQuery = 'SELECT * FROM users WHERE username = Nobody OR 1=1 -- AND password = ?'

But this won’t work because:

• The query is sent to the database with the username and password as separate values, not part of the SQL command itself.
• The database treats the username as just a value, not as SQL code. So, the query will look for a username of `Nobody OR 1=1 –` and a blank password, which will not work.

In other words, the SQL command is safe because the database already knows what the query should do, and it can’t be changed by the user’s input.

Important Notes

Even though parameterized queries are great for preventing SQL injection, they don’t solve all security problems. For example, they don’t stop XSS (cross-site scripting) attacks, where someone could enter JavaScript into the database. So, you should also use input validation to make sure users can’t enter dangerous code like JavaScript into your forms.

But using parameterized queries or stored procedures is a strong way to protect your site from SQL injection attacks.

Example Summary:

1. Normal Query (without protection):

sqlQuery = 'SELECT * FROM users WHERE username = ' + username + ' AND password = ' + password

This can be attacked with SQL injection like ' OR 1=1 --.

2. Parameterized Query (protected):

sqlQuery = 'SELECT * FROM users WHERE username = ? AND password = ?'

parameters.add("username", username)

parameters.add("password", password)

This prevents SQL injection because the database sees the username and password as values, not part of the query.

I hope this makes it clearer! If you have any questions, feel free to ask.