A little about SQL injection

I read some time ago on a website the following line: May the best of your past be the worst of your future. Amen to that brother!

Over the time I’ve seen good applications and bad applications and not so good application. The question that must be answered here is simple: How do we create a fairly safe PHP application?

At this moment I have knowledge of one major website that has serious SQL Injection issues, and at least 3 smaller websites that have the same problem. What is their problem you might ask. Well, it’s simple: by exchanging the user name when logging in as admin from the regular user name to something like ‘ or 1=1 # you can logging into the admin area and mess up with the whole website. How does this happens? Sloppy, lazy or unexperienced programmers it’s the answer. My advice to you: never stop learning and always be on guard. Maybe you can’t cover all the aspects of security but more that sure you can try.

So, what can happen if we have admin rights? Well, that can vary. Besides messing up the website by adding fake text and other such things, you may be able to damage the files or database. If the admin area allows the admin to upload files and those files are not checked, one can upload various scripts that can damage the website.

But what exactly is this SQL Injection. Well, imagine a simple login SQL like the one below:

SELECT * FROM admin WHERE admin='$admin'  AND password=MD5('$password');

Ok now. Image a user that inserts the following in the admin username field:

‘ or 1=1 #

Now let us see how our login SQL looks like. We will simply replace the $admin with the user name inserted by our good neighbor.

SELECT * FROM admin WHERE admin='' or 1=1 #'  AND password=MD5('$password');

All that remains of our sql if in front of #. Can you see the problem? Our fake admin says to mysql: select me the row where admin is empty OR 1=1. Well, in this world, 1=1 equals true no matter what. So we have a results set of 1 and this means that this guy is our admin so we let him in. Disaster!!!

How can we protect our scripts from that? I don’t know how others are doing it but I know how I do it.

Many programmers, after successful login, set up a variable in session like: $_SESSION['admin_logged']=1 and after that, they check in every page that this variable is set and has a value of 1, and if so, the user has access to the page. But if our user logged in with SQL Injection techniques, he’s not allowed to see our pages. Still, because he logged in, he triggered the $_SESSION['admin_logged'] variable to be true. So he can do what ever he wants with our defenseless website.

My method is a little different from that. When admin loggs in, I check his password using the following:

SELECT * FROM admin WHERE username='".$_POST['username']."' AND password='".$_POST['password']."';

So what’s the big deal you might say! It’s the same shit as the above SQL! Yes and no.

That’s true. But the magic happens from now on. Let’s assume he users SQL Injection techniques and loggs in. The whole login script is the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<?php
$sql="SELECT * FROM admin
        WHERE username='".addslashes($_POST['username'])."'
        AND password='".addslashes($_POST['password'])."'";
 
$query=mysql_query($sql);
 
if(mysql_num_rows($query)==1)
{
    $_SESSION['admin_name']        =md5($_POST['username']) ;
    $_SESSION['admin_password']  =md5($_POST['password']) ;
    header('Location:some_page.php') ;
}
?>

And the first line of somepage.php (and all the pages that require authentication) will start with the following lines:

1
2
3
4
5
6
7
8
9
10

<?php
session_start();
$sql="SELECT * FROM admin
        WHERE md5(username)='".$_SESSION['admin_name']."'
        AND password='".$_SESSION['admin_password']."'";
$query=mysql_query($sql);
 
if(mysql_num_rows($query)==0)
       die('No access dude');
?>

Let’s explain the script a little. So, when logging in, we check in the usual way if the admin is stored in our database. If he’s ok, we save in our session his user name and his password coded in MD5. This script assumes that you save password in your database coded in MD5. After logging in successfully, we redirect his to the admin page.

Let’s assume that somehow, the addslashes we used at login did not worked. Even if so (not possible though), every page that needs authentication will REALY authenticate our user since we saved using MD5 his user name and password in our session. So even if he tricks our first loggin script he won’t be able to see anything because we basically log him in on every page.

Well, that’s it. See you soon.

Was this useful? Show your support.