Preventing SQL injection (mySQL)

[LMC]

Hello all,
Ive been working on a site recently and made up this script to backslash unwanted characters. It also then can remove the backslash. This is pritty much like mysql_escape_string but with a few added words and characters/symbols.

Add this at the top of the page:

function escape($str) // Adds backslashes to exploitable text
    {
	  $str = strtolower($str);
      $search=array("\\","\0","\n","\r","\x1a","'",'"', ';', 'insert', 'select', 'delete', 'or');
      $replace=array("\\\\","\\0","\\n","\\r","\Z","\'",'\"', '\;', '\insert', '\select', '\delete', '\or');
      return str_replace($search,$replace,$str);
}
 
function remove($str) // Removes backslashes
    {
	   $str = strtolower($str);
       $search=array("\\\\","\\0","\\n","\\r","\Z","\'",'\"', '\;', '\insert', '\select', '\delete', '\or');
       $replace=array("\\","\0","\n","\r","\x1a","'",'"', ';', 'insert', 'select', 'delete', 'or');
       return str_replace($search,$replace,$str);
}

It is case sensitive, so if someone trys to get around it by typing InSERT or INserT it wont work.
You use this as follows, for inserting information in to the database:

$var = escape($_POST['name']);

For reading information from a database:

$var = remove($var);

Please dont flame me, im a beginner to PHP and only trying to help fellow members. Im sure theres easyer ways, if there is please tell me and I can improve on my skills.

 

[caja]

Nice anti-sql injection snipet codes, but there are a lot more, this is just a small example. Maybe you can add more snipet codes and make they more secure.

For example, here a very simple anti-sql injection

function antisql($sql) {
$sql = preg_replace(sql_regcase("(select|union|0x|cast|ex ec|varchar|insert into|delete from|update account|update login|update character|ugradeid|drop table|show tables)"),"",$sql);
$sql = trim($sql);
$sql = strip_tags($sql);
$sql = addslashes($sql);
return $sql;
}

 

[LMC]

Nice anti-sql injection snipet codes, but there are a lot more, this is just a small example. Maybe you can add more snipet codes and make they more secure.

For example, here a very simple anti-sql injection

function antisql($sql) {
$sql = preg_replace(sql_regcase("(select|union|0x|cast|ex ec|varchar|insert into|delete from|update account|update login|update character|ugradeid|drop table|show tables)"),"",$sql);
$sql = trim($sql);
$sql = strip_tags($sql);
$sql = addslashes($sql);
return $sql;
}

Hey, ive never done it that way before, but thanks for telling me

 

[SaintsIan]

Interesting concept, you can also check out Munin IDS

You must be registered to see links

, a PHP-based Firewall - they have a few other useful rules.

 

[caja]

I donwload it. Thanks for it. Didn't know that you can make that with php.
And the security of the firewall it's very nice

Thanks...

 

[LMC]

Will adding brackets around a string such as "(insert)" still act as another escape method?

 

[s-p-n]

mysql_real_escape_string should be used because there are multibyte characters that PHP converts into text which will cause a mysql injection...

Or, to be SURE you're not getting a mysql injection, use the MySQLi(MySQL improved) extension for PHP5.

I wrote a tutorial here on it.

Also, please see the PHP Manual.

Parameterized statements are the only way which makes sense to keep from getting injections.

See this brief article, [ame=http://en.wikipedia.org/wiki/SQL_injection]SQL injection - Wikipedia, the free encyclopedia[/ame]

 

[Sifex]

Hey Ian, Maybe sometime, you could help me with a web project going on...
Lol QR Reader

Lol yeah there are easier ways to get rid of backslashes and such, but going around the long way teaches you more stuff

 

[LMC]

S-P-N Thanks for the MySQLi link, I wouldnt mind trying it out just it would take some time getting used to it. IM gunna give it ago though.

 

[sachav]

@caja
Replacing insert, select, etc by empty text will not protect anything, as there is an easy bypass

INinsertSERT

will be replaced to become

INSERT

If you use PDO or MySQLi correctly (with prepared queries) you shouldn't worry about escaping anything.

By the way, here is a function that can be useful:

<?php
function escape_once($var) {
    
    if(function_exists('get_magic_quotes_gpc') && @get_magic_quotes_gpc())
        $var = stripslashes($var);

    if(@mysql_ping())
        return mysql_real_escape_string($var);
    elseif(@pg_ping())
        return pg_escape_string($var);
    else
        return addslashes($var);

}
?>

It prevents annoying over-escaping (so you don't end up with \' in your DB)

 

[yuie]

do this work for mssql?

 

[caja]

For basic SQL injections. That means yes, you can use on mysql or mssql.

 

[LMC]

do this work for mssql?

I think you would need to change

mysql_real_escape_string

to

mssql_real_escape_string

.

I suggest you all follow s-p-n' advice. Mysqli is actually easy to use once you get the hang of it, yea it takes longer to wirte out but its got a big speed increase and you know you aint gunna get any injections, AT ALL.

 

[lmame]

mssql_real_escape_string doesn't exist. You should use PDO and prepared statements (also for mysql though mysql_real_escape_string works nicely indeed):

You must be registered to see links

You can use BindParam to even go further and tell which type is the variable.

You must be registered to see links

Other scripts are perhaps useful to detect and log someone trying to hack you though real protection is somehow more useful

 

[Dread(nought)]

If you are serious about coding and want to be productive you should rely on a

You must be registered to see links

of your choice.

All of them have SQL injection prevention built-in.

 

[404]

Pretty neat. Thanks.

 

[Dread(nought)]

Welcome.

I might add that the use of an ORM will be weighting heavier at runtime and it definitely will take some longer before you get it working and will be productive.

But this is a sacrifice you will want to make since the possible benefits outweight the initial disadvantage.

Longterm benefits include separation of logic and CRUD, eased maintainability and, if the chosen ORM is decent, upwards scalability and extensibility.

One example that comes to my mind is

You must be registered to see links

and

You must be registered to see links

for the .NET platform. Hibernate is out for ~10 years, is heavily tested and enterprise-ready. NHibernate is a port of Hibernate and (sadly) a tad behind the development of Hibernate, but as mighty.

Both offer excellent SQL-generation and runtime, which will weight lighter on the SQL database compared to 99% of manually written SQL.

The best thing about (N)Hibernate is the possibility to load modules that do fancy stuff. For example there is a module that stores frequently accessed data in a

You must be registered to see links

intstance which will further reduce load on the SQL server and speed up your application.

(N)Hibernate supports a variety of RDBMS' and dialects.

---------- Post added at 10:38 AM ---------- Previous post was at 10:32 AM ----------

I might add that if you want to stick to or are stuck with PHP you might want to "harden" your installation with

You must be registered to see links

.

 

[lmame]

In 99% of time PDO or Mysqli prepared statements would be enough

 

[LMC]

In 99% of time PDO or Mysqli prepared statements would be enough

Thats what im currently getting used to at the moment . I wrote this thread before I found out about it. Thanks for the post anyway

 

[Apolva]

I'm sorry, I can't actually see any point in escaping MySQL keywords, and it isn't as simple as just putting a backslash before an apostrophe (ie. add_slashes will still allow some forms of SQL injection due to multi-byte characters).

mysql_real_escape_string() is the only reliable way to prevent SQL injection (other than using prepared statements).