Intro
Today we will learn how to do the SQL Injection attack, and how to prevent it.
Did you know hackers steal billions of dollars every year?
Did you know almost every 30 seconds is a new hackers attack?
If we are developers, we should always care about the security of our code (at least a bit). It doesn’t matter if we do front-end or back-end, both of them are vulnerable.
Today I would like to show you a bit about a SQL Injection attack, what it is, how to do it, why it’s dangerous, and how to protect your application from that.
Let’s start!
If you prefer video here is the youtube version:
What is SQL Injection
SQL Injection is one of the most popular OWASP vulnerabilities that is very easy to do and can do horrible damages.
It’s kind of like a situation when we can push some custom and unwanted commands to the SQL database.
For example, when we have username input, we can put come custom commands.
Like we can use deleting whole tables, getting data from other database tables or auto-unlocking admin account.
Why SQL Injection is dangerous
Data leak
By SQL Injection attacker can quickly get access to data that should never be accessible to the regular user.
For example, that can be your private messages, bank transactions, sensitive personal data like your ID, or where you live.
What worse, if database is vulnerable, attackers can have open access to millions of records in a moment.
Possible damages
This type of attack can give the possibility to fire any SQL command, not only getting data.
For example, an attacker can send money from your account to his own, change your account balance, or delete all the records.
That could be very painful, especially when no backup.
Unauthorized access
When we build an application, very often, we have some permission for x role.
Like only admin can delete or edit records, request to a friend needs to be accepted by an invited person, etc.
What if an attacker could go into the DB, and send messages from your account, add his account as a verified friend, or edit posts from your social profile?
Let’s perform an SQL Injection attack
Now we will make a simple attack to get somebody’s account access.
We built a banking app that you can test to attack, check the code, and responses to understand how it works fully.
Requirements to make SQL Injection attack:
Download our back-end code:
Here is the link to the code for Golang back-end for banking app:
https://github.com/Duomly/go-bank-backend
Clone it and go into this folder.
Setup your PostgreSQL DB:
You need to create a new Postgres DB.
Name it bankapp and use user Postgres, or you can use whatever user you want.
In that case, you will need to change the connection string.
Setup connection in the back-end code:
You need to set up your connection string in the two files, vulnerable-db.go, and migrations.go
Start migration:
Now you need to go to the main.go, comment API code, and uncomment the migrations one.
Your code of the main function should look like this.
func main() {
migrations.Migrate()
// api.StartApi()
}
Download our front-end code from:
In this step, we should go to the URL:
https://github.com/Duomly/angular9-tailwind-bank-frontend
Clone the app and go to the folder.
Next, we should install our dependencies by npm install and start the app by npm start.
Let’s start the fun:
In the last step of the config, we should uncomment changes that we did before, and start the API.
Our main function’s code in the main.go should look like this:
func main() {
// migrations.Migrate()
api.StartApi()
}
The last move is to run main.go by typing in the terminal:
go run main.go
Congratulations, you are ready to start hacking!
Standard credentials:
We have created two different users, and standard credentials for them are:
User 1:
user: Martin
password: Martin
User2:
user: Michael
password: Michael
What we will do:
As you can notice, we have a simple hashing and looking for a password that is already hashed.
So all of the tricks that we will put in the password input field will be transformed to hash (even if we will add there some SQL commands), which means SQL will not see them as the SQL commands.
In this case, we need to focus on the username input.
That could work because it is going into the SQL as a pure string.
I’ve especially created that to be vulnerable there.
You shouldn’t do it in your own app like this.
In the next section of the article, I will tell you how you should do it correctly.
Find user by username
To get the user by username, we can use one of these strings in the username input.
Of course, there are many more methods that you can use.
Martin' or username='Martin' AND 1 = 1 --
xx’ or username='Martin' AND 1 = 1 --
It works kind of like get the user by name Martin or name Martin and true, if we change the first Martin to Michael, we will get both profiles.
The second one works like get a user with username xx or username Martin and true.
Get all users
By using this string in the username input, we will be able to get all the records from the table named users.
Martin' or 1 = 1 --
This one method works like getting users with username Martin or true.
That means “or true” will get all other users because the condition is passed.
Get user by ID
We can take the user by his id when we use this one combination in the username input field.
It’s especially helpful when we look for admin (very often by default admins have ID 1).
xx' or id=2 AND 1 = 1 --
This one way works like get a user with username xx or id 2, and true.
What these symbols mean:
OR 1=1
This one means 1 is equal to 1, which is true. It’s like passing the condition.
'
This one mark is like an escape from the string.
That gives us the possibility to escape the string in the SQL and start to call the proper SQL command.
--
This one is kind of like a comment that makes SQL ignore the rest of the statement after these two dashes.
It’s very helpful, for example, when we have more fields that we send as well, like, for example, a password.
When we use this mark, our SQL will ignore info that the password should be verified as well.
How to secure the app from SQL Injection
Front-end form validation
Of course, a smart attacker shouldn’t have so many problems to avoid front-end validation.
Still, it’s something that will be the first obstacle.
You can add a special validation to your forms.
That can check is your email is equal to email, if your username has any special characters or length is not too big.
If you detect the suspicious combinations, you can block the form.
You can send the red flag to the back-end about a user with that IP/credentials tried to make some attack.
You can, for example, block his IP for a while, or ban him permanently.
Again, the attacker will know how to avoid your ban.
Still, it’s a new obstacle, and some people can resign from spending more time on your app.
Back-end variable validation
The next one idea how you can secure is the back-end that will verify all the strings and data are sent correctly and pass all the regexp validation.
It can be very helpful because it’s much more difficult to pass the back-end validation than the front-end one.
It’s helpful, especially if you add limits to the number of calls that users can make immediately.
Additional pass comparison like CompareHashAndPassword
This method can help you to stop sending data back to the attacker.
Of course, an attacker could do the damages like deleting something or change the records if you will let him do the SQL Injection.
Still, you can, for example, find the x user record in DB.
Next, auth the credentials, by using bcrypt.CompareHashAndPassword.
The app will not return the asked data to the client back and should throw a non-auth error.
Use SQL libs/frameworks
Using some frameworks like gorm for golang or sequelize for node.js can help you with the security a lot.
In most of them, there is an additional layer of security.
That will check if params are correct and build the proper queries from the data that the user sends to the back-end.
Declare types
This one is very crucial. By declaring tight types, you will avoid a lot of problems, not only these with security.
If you will set up all interfaces and types properly, there is almost impossible to send the wrong data back.
All of the data will be send in a 100% format that you would like to return to the final client (user).
Conclusion
Congratulations!
Now you know how to perform SQL Injection attack and how to secure your app from this one.
I hope it will be very helpful when you are building your own software.
You need to remember using it against apps that you have not permission from the owners is illegal.
Anyway, you can download our code, build your own app, or find virtual labs where you can do it and test.
Follow us to get more tutorials about coding.
Thanks for reading,
Radek from Duomly
Top comments (4)
Probably the most effective (and in most cases easiest) guards against SQL injection and regrettably not mentioned here is parameterisation of inputs. Just google it.
If you want a more in depth overview on this check out Troy Hunts course on SQL injection on PluralSight.
Let me troll a little...
Banks dont use PostgreSQL.
This isn't about SQL injection if it doesn't have a link to little bobby tables.
:D :D sure thing how I could forgot to add little Bobby tables :D
If I can fix that here is da link to the little Bobby tables company in the UK gov companies house registrar :D
beta.companieshouse.gov.uk/company...