parametrized sql?

Security issues related to php and mysql usage. How to make your code secure? Security measures and configurations? It's all in here!

Moderators: macek, egami, gesf

iam4423
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

parametrized sql?

Postby iam4423 » Tue Apr 24, 2012 12:14 pm

im trying to secure my site and a friend pointed out just how vulnerable it is to sql injection. and looking through various articles ive decided i want to use parametrized query's but i just cant get my head around them
heres my current code

Code: Select all

$body=$_POST['post_body'];
$thread=$_POST['thread_id'];
$forum=$_POST['forum_id'];
mysql_query("INSERT INTO `table` (Thread_ID,User_ID,Post_Content) VALUES ('$thread','$userid','$body')") or die(mysql_error());

how would i change this to secure it?

id appreciate any help anyone could give!

thanks

User avatar
egami
php-forum GURU
php-forum GURU
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: parametrized sql?

Postby egami » Tue Apr 24, 2012 12:53 pm

Code: Select all


$body
=$_POST['post_body'];
$thread=$_POST['thread_id'];
$forum=$_POST['forum_id'];
mysql_query("INSERT INTO `table` (Thread_ID,User_ID,Post_Content) VALUES ('$thread','$userid','$body')") or die(mysql_error());
 



That's no better than

Code: Select all


mysql_query
("INSERT INTO `table` (Thread_ID,User_ID,Post_Content) VALUES ('$_POST[thread_id]','$userid','$_POST[post_body]')") or die(mysql_error());
 


In fact, they are exactly the same, worded a bit differently.

To prevent SQL injections, just change a few simple things and make it work/look a little something like this...

Code: Select all


$body 
trim(mysql_real_escape_string(strip_tags($_POST['post_body']))); // kills all injections
$thread trim(mysql_real_escape_string(strip_tags($_POST['thread_id']))); // kills all injections
$forum preg_replace('/[^0-9]/','',$_POST['forum_id']); // removes everything except numbers..
mysql_query("INSERT INTO `table` (Thread_ID,User_ID,Post_Content) VALUES ('$thread','$userid','$body')") or die(mysql_error());
 



Enjoy.

User avatar
minimihi
New php-forum User
New php-forum User
Posts: 238
Joined: Sat Apr 14, 2012 11:57 am
Location: Vilnius, Lithuania

Re: parametrized sql?

Postby minimihi » Tue Apr 24, 2012 1:09 pm

Code: Select all


$mysqli 
= new mysqli('host', 'username', 'password', 'database');
$stmt = $mysqli->prepare("INSERT INTO table (Thread_ID,User_ID,Post_Content) VALUES (?, ?, ?)");
$stmt->bind_param('sss', $body, $thread, $forum);
$stmt->execute();
$stmt->close();
$mysqli->close();
 

there are more variations how to do it, just check references.

i - integer,
d - double,
s - string
b - blob :?

I read a little about mysqli, but it didn't do me good impression.
If you have decent class for handling mySQL functions, then there is no need to be scared of SQL injections. You can make your own parametrization.

Mine looks smth like that:

Code: Select all

$db = new Database();
$arg = array("body" => $body, "thread" => $thread, "forum" => $forum);
$qry = "INSERT INTO table (Thread_ID, User_ID, Post_Content) VALUES (:body, :thread, :forum)";
$db->Query($qry, $arg);
 

in Query function I check what value types are handed in and accordingly to type format it.
In the end it functions same way as egami wrote.

Reference:
Prepared statement
MySQL Improved Extension

iam4423
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

Re: parametrized sql?

Postby iam4423 » Tue Apr 24, 2012 1:53 pm

thanks to both of you i tried the method egami suggested but some characters still caused errors

ill try it the way minimihi suggets

thanks alot

User avatar
minimihi
New php-forum User
New php-forum User
Posts: 238
Joined: Sat Apr 14, 2012 11:57 am
Location: Vilnius, Lithuania

Re: parametrized sql?

Postby minimihi » Tue Apr 24, 2012 2:27 pm

If you're trying that mysqli, make sure it's installed/configured in your server. If not, you will need to install/configure it.

iam4423 wrote:i tried the method egami suggested but some characters still caused errors

try using htmlspecialchars() instead of mysql_real_escape_string() and strip_tags()

iam4423
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

Re: parametrized sql?

Postby iam4423 » Tue Apr 24, 2012 2:43 pm

how do i check the value types in query? i just dont understand how the array gets put into the query?

php

Code: Select all

$db = new Dbase_();
$arg = array("thread" => $thread, "user" => $userid, "body" => $body);
$qry = "INSERT INTO table (Thread_ID, User_ID, Post_Content) VALUES (:thread, :user, :body)";
$db->Query($qry, $arg);


class

Code: Select all

class Dbase_
{
function __construct()
         {   
   $db_user = "root";
$db_pass = "";
$db_database = "forum";
$db_host = "localhost";
$db_connect = mysql_connect ($db_host, $db_user, $db_pass);
$db_select = mysql_select_db ($db_database);
         }
   
 function Query($qry, $arg)
 {
 mysql_query($qry); //dont know what to put here
 }
   

}

sory for being a pain in the ass

User avatar
minimihi
New php-forum User
New php-forum User
Posts: 238
Joined: Sat Apr 14, 2012 11:57 am
Location: Vilnius, Lithuania

Re: parametrized sql?

Postby minimihi » Tue Apr 24, 2012 3:55 pm

If $arg is array, loop through all element
checking types using Variable handling Functions, format them and str_replace $qry.

Hm... seems it's no good. Injections must be killed at the very beginning, where data is received.
Just like egami did.

Now it's me who's pain in the ass. One who suggested totally wrong solution for secure parametrization. Dammit :-x
At least I successfully made you waste some time :D sorry about that

iam4423
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

Re: parametrized sql?

Postby iam4423 » Wed Apr 25, 2012 1:22 am

hmmm luckily i have the day off so i can spend it doing research! at least yiuve given me a good place to start from, if i get it sorted ill let you know

cheers

iam4423
New php-forum User
New php-forum User
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

Re: parametrized sql?

Postby iam4423 » Wed Apr 25, 2012 5:59 am

I've got it sorted and I'll whatever the code as soon. As my internet comes back online! (Been doing research on my mobile all day! I don't recommended it XD

Edit
if anyone is interested here's how i got it working

Code: Select all

$pdo = new PDO('mysql:host=localhost;dbname=forum','user','password'); //sets $pdo as a php data object with database connection details
$arg_np = array("thread" => $thread, "userid" => $userid, "body" => $body); //array of values
$stm =$pdo->prepare("INSERT INTO `table` (Thread_ID, User_ID, Post_Content) VALUES (:thread, :userid, :body)"); //prepares the statement
$stm->execute($arg_np); //runs query using array of values


simple as that http://php.net/manual/en/book.pdo.php tells you everything you need to know


Return to “PHP & MySQL Security”

Who is online

Users browsing this forum: No registered users and 0 guests

cron