Today, I'll show you some code vulnerable to SQL Injection. We will talk about good and bad practices. At the end I'll tell you which option is in my opinion the best one and why.
So... Let's get started...
Remember!
Do not do anything that has been shown here in real applications; you may face legal and financial consequences.
To try to do something with SQL, we need a new table in our fake database.
Now, have a look at this ugly code…
What do we have here?
We have a POST endpoint /login which receives two strings, email and password.
This endpoint connects to the database and executes a command to select the first match of a user where the email and password (in plain text for testing purposes 🙃) match the provided data.
app.MapPost("/login", (string email, string password) =>
{
const string connectionString = "Data Source=localhost;Initial Catalog=TestSqlInjection;Integrated Security=true;TrustServerCertificate=true;";
using var connection = new SqlConnection(connectionString);
using var command = connection.CreateCommand();
command.CommandText = $"SELECT * FROM USERS WHERE Email = '{email}' AND Password = '{password}';";
connection.Open();
using var reader = command.ExecuteReader();
reader.Read();
return new User(Convert.ToInt32(reader["Id"]), reader["Email"].ToString()!);
})
.WithName("Login")
.WithOpenApi();This code has couple of minor errors.
Command is concatenated from user’s data without any validation or using sql parameters.
Okay, let’s add some users
SELECT TOP (1000) [Id]
,[Email]
,[Password]
FROM [TestSqlInjection].[dbo].[Users]
okay, there we have it. Let’s try to log in to our application with email “tester@example.com” and password “qwe“.
It looks like it’s working, doesn’t it? Don’t fall for it.
There are many things that can be done with our dirty backend code. Try logging in as “tester@example.com“ without knowing its password.
Let’s enter the following code as the password.
' or 1=1--Explanation:
SELECT * FROM USERS WHERE Email = ‘{email}’ AND Password = ‘{password}‘;
The bolded part will be replaced with the password provided by the user. So, we are using a ‘ (single quote) as the end of the string. Do you see it? In the SQL clause, we have:
AND Password = ‘{password}‘;
When we use a single quote as the first character, we end this clause. Then we start a new clause, OR 1 = 1, which is always true.
What is the role of the — (SQL comment syntax) at the end?
Without this, our query would end with a single quote character, which is invalid SQL. We have to comment out the rest of the code after the password to ensure that our query will end with OR 1 = 1. Without this, we would get the following error:
Unclosed quotation mark after the character string ‘;
‘.
So the final clause is
SELECT * FROM USERS WHERE Email = 'tester@example.com' AND Password = '' or 1=1--';Without security measures, someone could steal sensitive data. We can log in as any registered user.
If what we just did happened in a real application, it would definitely be a breach of GDPR in Europe.
Let’s get back on topic, though. Is that all we can do?
How about destroying data?
Of course system hacker can use that trick to destroy our data. Have a look:
'; update users set password = 'newpass'--The final clause is
SELECT * FROM USERS WHERE Email = 'tester@example.com' AND Password = ''; update users set password = 'newpass'--';And the result? Please
Our application has a bug. When no user has been found, we’re getting an exception, but that doesn’t matter because I wanted this application to have nothing but the essentials for testing our SQL.
Okay, what’s stopping us from clearing our table? Nothing.
Let’s check the following password
'; delete from users--The final clause
SELECT * FROM USERS WHERE Email = 'tester@example.com' AND Password = ''; delete from users--';Of course there’s an exception in our application but what’s happened in our database?
We lost all of them.
[…] I recently wrote an article which is the first part about SQL Injection SQL Injection in C# Application: An Example. […]