Hacking the Web With SQL Injections
Focus on SQL Injections, PHP, and the Diwa Project
I always wanted to be a hacker. For 6 years, I have written applications in Php, JavaScript, and Python. During my studies, teachers spoke about SQL Injections and how to prevent them. But I never performed attacks such as SQL Injections, Broken Authentication, or Cross-Site Scripting. It was a missing piece in my knowledge and I was looking for an opportunity to learn more about them. So when someone in my team recently came to me and offered to do some research, I was the happiest man on Earth.
This post will have a follow-up. It’s just the beginning of me hacking the web, learning about security breaches, and having a lot of fun. In this post, I will speak about a specific type of injection: SQL Injections. I will cover the history, explain the ins and outs, perform attacks, and tell you how you can secure your applications. We will use Php and MySQL. If you use another programming language, don’t be sad: more content is coming.
Now, put your hood on and launch your favorite electronic music playlist, and let’s start hacking!
How SQL Injections Work
Before talking about SQL Injections, let’s talk about Injections first. Injections are one of the oldest and more common attacks. Injection attacks are malicious code that is injected into the network and their goal is to fetch information from databases. They target web applications (not only Php applications) and can lead to data theft or loss. In the worst scenarios, they can compromise your entire system: you will no longer be able to log in and access your services. In other words, this is bad. I mean really really bad. Imagine that you administer a hospital, you have hundreds of patients who are waiting for surgery, and you record their security numbers, their emails, and other sensitive information. Injections can block your whole system and hackers can get, sell, and use this information. There are different categories of injections, such as SQL Injections, Cross-site Scripting, or CRLF injections. This article focuses on SQL Injections.
How do Injection attacks happen?
Well, the primary reason is insufficient user input validation. In other words, it means that your code is insecure from the developer side and your queries are unsafe. If you want to fix them, you need to stop using dynamic queries and securing your code when a query is executed. This is why we use prepared requests in Php because they prevent us from SQL Injections.
The code above comes from the Diwa Project and is located in the model.php file. Look at line 16, this is where the security breach is. This line of code executes the following statement in SQL:
'SELECT * FROM users WHERE email = ‘$email’ AND password = ‘$password’;'The purpose of this line is to verify if a user exists in the database and if this is the case to return it. Inside a form, an attacker could write 'OR '1'='1'' , this will execute the following query:
'SELECT * FROM users WHERE email = ‘$email’ AND password = ‘$password’ OR ‘`1’ = ‘1’’;'This is a valid SQL Statement and since '1'='1' `’1’ = ‘1’` is always true, the query will return all rows from the users' table. If you do this inside the Diwa project, you’ll be logged in as an admin (we will do this in a moment). But you could do more, you could drop a table and even perform a DoS (denial-of-service) attack if the users table is large and contains millions of rows. For example, you could add thomas@example.com'; DROP TABLE users; . It will execute the following code:
'SELECT * FROM users WHERE email = thomas@example.com'; DROP TABLE users;Why does it work? Because you are finishing the first SQL statement and adding another one just after. Yeah, I know, insane.
How do you prevent SQL Injections in Php?
This is one rule of thumb: always validate user input. Most of your users don’t want to break your website, the Internet is not full of hackers. However, you should never build SQL statements directly from user input. There are two methods you can use:
- Escaping your strings with functions such as real_espace_string. This function will escape special characters in a string for use in a SQL statement.
- Using prepared requests that will allow you to prepare an SQL Query with empty values as placeholders, then bind variables to the placeholders, and finally execute the query. The code snippet above is a prepared request. Prepared requests can also reduce parsing time because the preparation of the query is done only once.
Luckily most Php frameworks, such as Symfony and Laravel, use prepared requests for you and when you do simple requests, you will use an ORM, Doctrine for example, and it will be more secure. Problems happen when you write raw queries.
Deliberately Insecure Web Application as an Example
There are tons of projects on the Internet if you want to learn more about security. Luckily the OWASP’s website keeps a list. There you find examples in many many different programming languages. For a few reasons, I decided to start with Php and the Diwa project:
- The project uses Docker and works outside of the box. You clone it from GitHub, build the docker image, run the container and you are ready to go.
- It is also simple to understand. Soon we will cover more complex projects but it is always better to start with simple projects, especially when learning something new.
- Diwa is written in Php and it tends to be a programming language used by many. I am not here to tell you if Php is bad or not. I can however tell you that Php is used by 78.9% of all websites. These are the facts.
- Initially, the project uses SQLite but I wanted to use MySQL instead. It was really simple for me to edit the configuration and add docker-compose and MySQL.
Let’s start by building the project locally before looking at the modifications I made. Make a copy of the .env.example and name it .env . You don’t need to add anything inside: we will use it in another post.
$ docker-compose buildStart by running the following command. It will build the Php image and install the libraries, including pdo and pdo_mysql .
$ docker-compose upThen, launch your containers. This command creates three containers: the Diwa’s app, the datadog-agent (no need to look at it today), and the MySQL Database. Once all images have been pulled, go to http://localhost:8080.
Click on “Setup Diwa!” button: it creates the database tables and adds content inside. Congratulations, Diwa is now up and running!
Before we perform attacks against Diwa, you should know that I made a few modifications to the project source code.
I updated the config.php file in order to use MySQL instead of SQLite. I also added pdo librairies inside the app container. They are inside the Dockerfile.
I also fixed a typo at line 10 by changing the $config['database']['host'] to $config['database']['server']$.
Let’s hack our project!
Time to Hack and Perform SQL Injections
First, if this is not the case, launch the project with docker-compose with docker-compose build and docker-compose up. Look at the “E-Mail-Address”
and “Password” inputs at the top of the screen.
Start by clicking on the Register button and create an account. The invitation code is 3702. You can find it in the config.php file.
You should now be logged in. If not, it means that something went wrong when bootstrapping the project. I suggest you ask your questions in the comments section. You can now log out and go back to the home page. Try to write invalid credentials and look at what happens. You should see the following error message: “Wrong E-Mail-Address or Password”. Now I want you to write '-- in the email input and some random characters in the password field.
You should have a nice 500 error. This means that something bad happened to the server, something so bad that the server produces an error. That also means that it tells us that maybe this website is insecure. We are now going to try some attacks with a nice cheat sheet I’ve found. Look at the Bypassing Login Screens section and try to write some of them. For example, the following commands seems to cause an error: admin'-- , 'admin'/* , or 1=1-- . This one 'or 1=1#' will log you as an administrator. Depending on the database installed, some of the commands will change. But here to be simple, you first close the first part of the query with the `’` and inject an always true verification. You could also write '1=1; inside the logging for and it would work.
And yes, you will be able to see the list of users and take various actions, such as editing their credentials, promoting users as administrator, and even deleting their accounts.
We are now capable of hacking the logging page, what about dropping some tables, the user tables for example. There is something I already know that an attacker wouldn’t: I know tables are prefixed with diwa. For example, the users table is diwa_users . This is a good practice when creating tables: don’t call them just users or products . Try to add a prefix, your-project_table for example, this will make your app more secure. I asked you earlier to create an account. This is where we are going to use it. I want you to ask your email address and password but before validating, add the following after your email address: 'DROP TABLE diwa_users; . It should look like this. Now take a deep breath and hit enter.
Look at the screenshot below. I dropped the diwa_users table, I am no longer able to log in and sign up.
And if you look at the database, the diwa_tables are now gone. Scary, right?
Reset up your database and recreate an account. We are now going to make the database sleep. Instead of adding the DROP TABLE, add AND sleep(20); at the end of the login input and hit enter. Did you notice that your request is taking much longer than it should? It happens because the “sleep” command is executed. With that in mind, you should now try to create an infinite loop and make the database sweat! You can add our answer in the comments section, I will tell you if you are on the right track!
Catching up
I really hope you like this content SQL Injections. You should now know the basics on how to perform attacks and prevent them. I am currently writing a tutorial on how to set up an observability tool to get alerted when attacks happen. Special thanks to Tim Steufmehl for creating this project and Tambi Gumbo for your review.
The next attack I am going to cover will probably be cross-site scripting. I don’t know if I will be using this project or another one. In any case, I’ll apply the same structure: description of the attack, how to prevent it, and perform example attacks.
Have a nice day, folks!
