How to Protect Your PHP Website from SQL Injection Hacks?
Please note: I am not an ‘absolute’ expert, but none of my projects have ever been hacked (yet), are SQL Injection proof (as far as I know), and I love to learn. I guarantee nothing.
What Is SQL Injection and How Is It Used?
Basically, SQL Injection is a method used against websites and applications to gain access to the website’s or application’s data, stored in a SQL database. SQL Injection is used to gain access to a database’s information (or an entire company), to destroy a database’s information, or to manipulate a database’s information. It is a method used to exploit the security vulnerability of an application or website. There are different types of SQL Injection, but in this article we will only cover the basics.
Let’s see how it is used, to further understand what it is. I am going to use PHP as my scripting language in these examples. You can use substitute any language(s) you use. The focus should be on the SQL commands.
Example
Suppose you are a professional with your own business. You have created an SQL database with a table that contains all of your clients’ information, that you use to send out important notifications, billing, etc. It took you an entire year to gain 50,000 very important clients. You manage your database by logging in online, as you travel, and doing whatever you need to do, directly from your website.
Your SQL query in your PHP log-in script, on your website:
<?
$q = “SELECT `id` FROM `users` WHERE `username`= ‘ ” .$_GET[‘username’]. ” ‘ AND `password`= ‘ ” .$_GET[‘password’]. ” ‘ “;
?>
One day a self-proclaimed hacker stumbles upon your website. He clicks the ‘Log In’ button.
He enters the following in the ‘username’ field:
‘ ; SHOW TABLES;
The hacker now has been shown every table you have in your database.
Since he knows your table’s name, he enters :
‘; DROP TABLE [your table’s name];
All of your information is gone.
Note: There are attempts that are much more complicated than this, and someone can spend a lot of time to get into your database, or they can even use a program to try to exploit the vulnerability of your website, database, application, etc.
Step 1 Use mysql_real_escape_string()
This PHP function escapes special characters for use in SQL queries and protects you from attack.
The query would now look like this:
<?
$q = “SELECT `id` FROM `users` WHERE `username`= ‘ ” .mysql_real_escape_string( $_GET[‘username’] ). ” ‘ AND `password`= ‘ ” .mysql_real_escape_string( $_GET[‘password’] ). ” ‘ “;
?>
Step 2 Use mysql_query()
Using ‘mysql_query()’ has additional protection against SQL Injection. A query not wrapped in ‘mysql_query()’ could allow a hacker to use multiple SQL commands from your ‘username’ field, instead of just one, which is another vulnerability. ‘mysql_query()’ only allows one command at a time.
So, our query would now look like this:
<?
//connection
$database = mysql_connect(“localhost”, “username”,”password”);
//db selection
mysql_select_db(“database”, $database);
$q = mysql_query(“SELECT `id` FROM `users` WHERE `username`= ‘ ” .mysql_real_escape_string( $_GET[‘username’] ). ” ‘ AND `password`= ‘ ” .mysql_real_escape_string( $_GET[‘password’] ). ” ‘ “, $database);
?>
Recommendation: Centralize Your Connections
In your script, you should centralize your connections to one page.
On each page that needs it, just use the ‘include()’ function to include the page that hosts your SQL database connection information. This would force you to create queries with the same format on every page you create, and reduces the chances of a mistake leaving a vulnerability open.
So, let’s say we make a page called ‘connections.php’ and put in the following:
<?
//connection
$database = mysql_connect(“localhost”, “username”,”password”);
//db selection
mysql_select_db(“database”, $database);
?>
We could modify our query using the new setup. Our log-in page would have:
<?
include(“connections.php”);
$q = mysql_query(“SELECT `id` FROM `users` WHERE `username`= ‘ ” .mysql_real_escape_string( $_GET[‘username’] ). ” ‘ AND `password`= ‘ ” .mysql_real_escape_string( $_GET[‘password’] ). ” ‘ “, $database);
?>
Recommendation: Clean Data at the Beginning of the Page
Many programming languages force you to declare variables before you can use them throughout the script. PHP does not force you to do this, however, it’s a good habit to clean out your variables at the beginning of the page anyway!
Sure someone can ask, “If I’m cleaning each variable throughout the page, why should I clean the variables at the top? Aren’t I doing the same thing with your recommendation?”.
It is easier on you to clean variables at the beginning of the page for a few different reasons, beyond formatting.
- It reduces the amount of code you have to write.
- Once the variable is clean, you can use it freely throughout the page, without the fear of vulnerabilities.
- It is cleaner and more organized, allows you to work easier, and avoids mistakes.
If we cleaned variables at the beginning of the page, our script would look like this:
<?
include(“connections.php”);
$username = mysql_real_escape_string( $_GET[‘username’] );
$password = mysql_real_escape_string( $_GET[‘password’] );
$q = mysql_query(“SELECT `id` FROM `users` WHERE `username`= ‘ ” .$username. ” ‘ AND `password`= ‘ ” .$password. ” ‘ “, $database);
?>
You could even go as far as creating a function to do all cleaning for you, reducing the amount you have to type further. Look at the following example.
<?
function cleaner($input){
//clean variable, including mysql_real_escape_string()
}
include(“connections.php”);
$username = cleaner( $_GET[‘username’] );
$password = cleaner( $_GET[‘password’] );
$q = mysql_query(“SELECT `id` FROM `users` WHERE `username`= ‘ ” .$username. ” ‘ AND `password`= ‘ ” .$password. ” ‘ “, $database);
?>
Recommendation: Check Even After It Is Cleaned
You can have additional checks in place to guard against unnecessary processing on your server. This is achieved by adding checks to your script before you ever get to the point of running the query; only running the query when you find the data acceptable.
<?
function cleaner($input){
//clean variable, including mysql_real_escape_string()
}
include(“connections.php”);
$username = cleaner( $_GET[‘username’] );
$password = cleaner( $_GET[‘password’] );
//Check if the input is blank.
if( ($password == ”) || ($username == ”)){
//dont let them pass
}
//Check if they are putting in way too many characters than should be allowed.
else if( (strlen($username) > 20) || (strlen($password)> 20) ){
//dont let them pass
}
//Passed all of our checks! Run query.
else {
$q = mysql_query(“SELECT `id` FROM `users` WHERE `username`= ‘ ” .$username. ” ‘ AND `password`= ‘ ” .$password. ” ‘ “, $database);
}
?>
That’s pretty much it.
If you have any questions, feel free to ask!