Omnimaga

General Discussion => Technology and Development => Web Programming and Design => Topic started by: Ikkerens on December 01, 2010, 01:06:50 pm

Title: MySQL injection prevention
Post by: Ikkerens on December 01, 2010, 01:06:50 pm

Allright, I wrote this little snippet to prevent mysql injection.
And then I thought of the fact that alot of websites are still sensitive to mysql-injection, hence I came on the idea of sharing this script.

Code: (php) [Select]

<?php

function sql_query($query, $variables)
{
//Take the query, and replace the contained variables
//Query should contain [WHERE]
if ( !preg_match(&#39;[WHERE]&#39;, $query) )
{
die();
}
else
{
$parms = Array();
foreach( $variables as $name => $variable )
{
$parms[] = "`".$name."` = &#39;".mysql_real_escape_string($variable)."&#39;";
}
//Merge parameters
$parameters = implode(&#39; AND &#39;, $parms);
//Prepare the query
$query2 = str_replace(&#39;[WHERE]&#39;, &#39;WHERE &#39;.$parameters, $query);
return mysql_query($query2);
}
}

?>

And the usage:

Code: (php) [Select]

<?php

include(&#39;./sql_query.php&#39;); //Or any way to implement the function.

$query = sql_query(&#39;SELECT * FROM `table` [WHERE]&#39;, Array(
"field1" => $_POST[&#39;username&#39;],
"field2" => &#39;value&#39;
));

?>

In the array, the key name is the field name in SQL.
Then the value in the array is the one that should be found.
For now, I do not support the OR attribute, and I never use that one personally xD

Title: Re: MySQL injection prevention
Post by: calcforth on December 01, 2010, 01:38:57 pm

Something is wrong in this picture here. Why do you feel the sudden urge to reinvent the wheel?

Naive SQL injections are closed once and for all if you use prepared statements (http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html). You can do it in PHP with mysqli_prepare (http://www.php.net/manual/en/mysqli.prepare.php), pg_prepare (http://php.net/manual/en/function.pg-prepare.php), ibase_prepare (http://www.php.net/manual/en/function.ibase-prepare.php), etc.

Title: Re: MySQL injection prevention
Post by: Ikkerens on December 01, 2010, 01:44:19 pm

Quote from: calcforth on December 01, 2010, 01:38:57 pm

Something is wrong in this picture here. Why do you feel the sudden urge to reinvent the wheel?

Naive SQL injections are closed once and for all if you use prepared statements (http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html). You can do it in PHP with mysqli_prepare (http://www.php.net/manual/en/mysqli.prepare.php), pg_prepare (http://php.net/manual/en/function.pg-prepare.php), ibase_prepare (http://www.php.net/manual/en/function.ibase-prepare.php), etc.

I see what you mean there, but those are functions for MySQLi, PostGreSQL and IBase.
What I'm doing here is basically using the default MySql function.
But then again, my sole purpose is to let people realise that their website is leaking.

Title: Re: MySQL injection prevention
Post by: DJ Omnimaga on December 01, 2010, 02:31:24 pm

I assume this is mostly to show the right way to prevent SQL injections on your site, right? Usually most sites or softwares are updated to prevent them, but I guess it's good to know how to prevent them if you're learning PHP and are making a website.