Hello there! Today we'll be learning PHP security. Now, this article is aimed to prevent SQL injection on your website/webapp. Before reading this article, you should have a basic knowledge of PHP, SQL, HTML, a little CSS (for we'll be designing our sample web app a little bit) and XAMPP. If you have no idea what those are, please learn the basics of those first before delving into this tutorial. Alright, without any further ado, let's begin!
What is SQL injection?
SQL injection is a kind of attack wherein the attacker "injects" SQL commands into the input field and if the website is vulnerable, the attacker can alter, edit, delete and dump data from the Database.
How SQL injection works?
A normal user would just go and input a normal value. For example, you have a website that lets users view types of fashion depending on the country. The normal user might enter a country let's say "Philippines", after the user enters that, the database would return information about fashion and clothing about that country.
The SQL query for that would be:
"SELECT * FROM fashion WHERE country='Philippines'";
Malicious users on the other hand would enter something like this on the input field ' OR 1=1
The SQL query for that would be:
"SELECT * FROM fashion WHERE country='' OR 1=1'";
Since 1 is always equals to 1, the database would then keep on retreiving all the data and therefore dumping it all to the attacker.
Applying it.
Alright, now let's apply what we learned. I created a simple and vulnerable web app that would ask users the country that they want to know about its clothing style. But now the problem is that, let's say you want to know which countries are only available on the database. Now you might be thinking, "Why not just type all of the countries one by one so I would know which exists or not". Well, it is possible but that is very time-consuming and boring. Imagine typing all the countries one by one. We can use a simple and basic SQL injection attack to let us know which countries are available on the database.
But now, we want ALL of the data inside the database all at once. How do we do that? That's where SQL injection comes to light. Let's go back to the web app and let's try to insert a SQL statement.
As you can see all of the data in the database was dumped to us. Since 1 is always equals to 1, the Database server would then continously grab data and give it to the attacker and thus giving the attacker a data dump.
Code Review
Before we apply the security on how to prevent this, let's first take a look at the source code.
If ever you have a password set, include it on line 4 beside 'root' on between the single quotes. As you can see I did not set any password so I left it blank
Go to xampp/htdocs and create a folder named "demo" and save the file there with the name "index.php"
So the path would be xampp/htdocs/demo/index.php
Now for the Database setup and configuration, open up XAMPP and Click start on Apache and MySQL after that click on Shell
It will still ask you to enter your password even if there is no password that is set. If that's the case, just hit enter again.
Now, follow what are on the following images below.
(Don't just copy it, think about it and understand it while typing it for you to learn)
Alright, I decided to put all the INSERT command in NOTEPAD++ to make it quick for me to finish this, but you, as someone following this tutorial, type all of this one by one in the Shell.
Alright. You're all set! Try practicing the basic SQL injection on your newly built web app. Before proceeding to the next part, go ahead and poke around a bit.
Now you might be thinking, "Why did I not just put all the codes in a .txt file?". Well it's because I want you to have a knowledge of the web app you've created by making you type it all down so you could understand how it works. Plus, how would you learn if you'll only copy+paste it?
(I would not be explaining the code thorougly, I would only explain the important parts. Like I said in the beginning you need to have some basic knowledge of PHP, MySQL, HTML and XAMPP)
Securing our Vulnerable website
So far, the web app we've created is vulnerable and has a security flaw. On this part we would be securing our web app in two ways.
1. The very basic, we'll use the "mysqli_real_escape_string()" function to strip/sanitize user input to prevent users from adding SQL statements to our database
2. We'll use a "Prepared statement". Prepared statement is much more complex than the normal mysqli_real_escape_string()
Alright, here's the explanation I got from w3schools.com and php.net to explain those two I mentioned
1. mysqli_real_escape_string() -
The mysqli_real_escape_string() function escapes special characters in a string for use in an SQL statement.Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection2. Prepared statements -
A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.
Prepared statements basically work like this:
Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example: INSERT INTO MyGuests VALUES(?, ?, ?)The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing itExecute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values
Here's my own explanation
mysqli_real_escape_string(). It just removes the special characters the attacker put in the input field. So putting ' OR '1=1 in the input field would be useless for the special characters were escaped.
Prepared statements. Before you send a query to the database, if your web app is coded to have a Prepared statement, it already has a prepared SQL query to send to the database. Like a template. So adding a SQL statement to conduct SQL injection in the input field would not be effective.
Putting the Security in practice
So it's time to apply the security we learned in our code. If you've finished understanding the code, go to your editor and we would change some of it.
"Applying mysqli_real_escape_string()"
Let's first apply the "mysqli_real_escape_string()".
Let's update the code on Line 83. Which is this one
Change it to this and save it
Now, go back to the web app and try to conduct a SQL injection and you'll see it won't work anymore.
SQL injection is not working anymore. Also try other format such as ' OR '1'='1' , OR 1=1 , ' OR '1=1'--
What Just Happened?
When applying mysqli_real_escape_string(), you should put it on the Global Variable that would catch the query string thrown by the user on the input field. In our case it is "$country = $_POST['ctry'];" and it became "$country = mysqli_real_escape_string($link,$_POST['ctry']);". What happens is whatever value the user inputs, mysqli_real_escape_string() escapes the special characters before it is queried into the Database.
"Applying Prepared Statements"
So, let's now apply Prepared Statement in our code. Go back to line 83 and we would be changing it. So far, this is what the code on line 83 looks like.
(Go back to Code Review and delete the codes on lines 90-115 for we would not be needing them anymore)
Now change it to this. (The explanation is included on the image)
So that completes our Prepared Statements. Once again, go back to the web app and try injecting it with SQL statements and once again it won't work.
So you might be asking now "Which is much better to use?". Well, according to the research I made, most developers recommend Prepared statements than mysqli_real_escape_string() for the fact it has much more ways in securing your web app.
There are many dangers your website faces when it is vulnerable to SQL injection. Some examples are:
1. It can be defaced
2. The attacker can modify your database. Such as Deleting, Editing and Inserting values
3. Sensitive information will be leaked
4. There is no such thing as "Privacy" anymore
2. The attacker can modify your database. Such as Deleting, Editing and Inserting values
3. Sensitive information will be leaked
4. There is no such thing as "Privacy" anymore
On my experience in hacking even until today, there are still many websites vulnerable to SQL injection and some websites are even vulnerable to a simple 'OR 1=1. I was even successful on defacing a website with the help of a friend just because the website was vulnerable to SQL injection. So make an effort in securing your website.
The simple SQL injection attack we did also works on username and password fields if the Website is unsecured. There are also other ways in securing your website against SQL injection but I only covered the basics. If you want to know the other ways, Google is out there to help you. Also, in the Further Reading section below, I provided links about this topic.
The SQL injection we did is just very basic and very simple. There are many ways to conduct SQL injection attacks and there are also advanced techniques on how to do it and also there are many types such as: Blind SQL injection , XPath SQL injection and more.
When I am hacking a website that is vulnerable to SQL injection, my personal favorite tool is SQLMAP.
It is a built-in tool on many hacking OS such as Kali Linux and Blackarch Linux (Those two are my favorite) and more.
SQLMap -> "http://sqlmap.org/"
Kali Linux -> "kali.org"
Blackarch Linux -> "blackarch.org"
On the next article, it would be about XSS and how to prevent it.
Alright! That's it! We're finished! I hope you guys enjoyed reading and I hope you guys learned.
Special thanks to Anonymous Philippines and Team Ghost India.
"Hack the Planet!"
"Knowledge is FREE!" :)
Further reading
More about SQL injection, visit this site
"https://www.owasp.org/index.php/SQL_Injection"
For other attacks, visit this
"https://www.owasp.org/index.php/Category:Attack"
More about Prepared statements
"http://php.net/manual/en/pdo.prepared-statements.php"
"http://www.w3schools.com/php/php_mysql_prepared_statements.asp"
More about mysqli_real_escape_string()
"http://www.w3schools.com/php/func_mysqli_real_escape_string.asp"
"http://php.net/manual/en/function.mysql-real-escape-string.php"
Here's a great e-book about PHP programming and MySQL by Kevin Yank. I have read this book and it is good. "http://www.allitebooks.com/build-your-own-database-driven-web-site-using-php-mysql-4th-edition/"
If you want to learn more about PHP security, here's a great e-book for you guys to read
"http://www.allitebooks.com/pro-php-security-2nd-edition/"
If u have any queries... follow my profile and ask me what you want to know.
Comments
Post a Comment