SQL Injection

[alirezaok]

my site hacked. i traced the hacker and i found he used of sql injection to hack my website.

i have this path in my website:

index.php?type=gallerylists&lang=1&id=1

he used of:

index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(53)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(54)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(55)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(56)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(57)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(97)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(98)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,30,1)=char(99)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,31,1)=char(48)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,31,1)=char(49)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,31,1)=char(50)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(48)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(49)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(50)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(51)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(52)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(53)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(54)%20and%20id=2
index.php?type=gallerylists&lang=1&id=1%20AND%201=2%20UNION%20SELECT%201,1,1,1,1,1,1,1,1,1,1%20FROM%20user%20WHERE%20substring(password,32,1)=char(55)%20and%20id=2

and very of these same urls to access the password of my website's control system.

how do i prevent of this sql injection?
does $id=mysql_real_escape_string($id) perevent?

i am wating.
thanks

[Piranha]

Yes, using mysql_real_escape_string prevent SQL injection attacks. But you have to use it in EVERY variable that you get from the outside in EVERY query on your site.

Of course you should change passwords, and probably hash them as well to have a little more protection for the passes. Look in this thread to get some more information about what you should do now.

[alirezaok]

mysql_real_escape_string() prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

but there is not any of these characters in url string

[bretticus]

Wow, that is a wild hack! I'm still sure how inserting crud like

type=gallerylists&lang=1&id=1 AND 1=2 UNION SELECT 1,1,1,1,1,1,1,1,1,1,1 FROM user WHERE substring(password,31,1)=char(50) and id=2

amist your SQL query got the hacker into your database.

And you are right about mysql_real_escape_string. It won't strip out those spaces (%20). One security practice I have adopted for injecting ids into queries is based on the simple fact that (most of the time) the id is an integer. I have a function for this that I reuse:

function format_safe_mysql_id($id)
{
	return ( is_numeric($id) ) ? intval($id) : 0;
}

Of course, you can just use intval() by itself as a non-numeric string should always return 0. I just use is_numeric first because of the documentation that states: "non-empty arrays and objects return 1". Which basically means I'm just paranoid.

But I strongly encourage your to use mysql_real_escape_string() in any situation where you have remotely accessible variables that can be injected into your SQL queries.

Good luck!

[Piranha]

You are right, there is none of those characters in the string. To avoid numbers messing it up you can use any of 4 ways:

1. Bretticus way and not use ' signs. It is perfectly safe as long as you remember to use the function for every int (and simular functions for other numeric values).

2. Bretticus way and use ' signs. It does the same thing basically.

3. Use mysql_real_escape_string and use ' signs. Like this:

   $id = mysql_real_escape_string($_GET['id']);
   $sql = "SELECT column FROM table WHERE id = '$id'";

   It is as safe as Bretticus way, but you really need the ' signs otherwise you already know what the result may be.

4. Be paranoid. Use mysql_real_escape_string, Bretticus way and use ' signs. The advantage with this method is that if you by mistake treat it as a string it still will work. The same thing if you forget to do one of the checks.

There is not one way that is right and one that is wrong. I personally use the 3rd way since I haven't come around to write methods for all the different kind of values that does not need the ' signs. When that is done I think that I'm gonna be paranoid... 😉

You can of course add more security by checking IP and don't allow IPs that have used values that are not allowed. Then anyone that tries to change the variables themselfes will only have one attempt. This could be a good idea on a large site or a site that is constantly attacked by the same IP. But it is to much work for smaller sites.

[Weedpacket]

Or use [man]PDO[/man] to write prepared queries and have the db drivers sanitise everything...

[bretticus]

Weedpacket wrote:

Or use [man]PDO[/man] and have the db drivers sanitise everything...

wow, and transaction support to boot! I really need to make the PHP5 switch!!!