Board index   FAQ   Search  
Register  Login
Board index php forum :: PHP and MySQL Security PHP & MySQL Security

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

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
iam4423
New php-forum User
New php-forum User
 
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

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
egami
php-forum GURU
php-forum GURU
 
Posts: 2197
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

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
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 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
iam4423
New php-forum User
New php-forum User
 
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

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()
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 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
iam4423
New php-forum User
New php-forum User
 
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am

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
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 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
iam4423
New php-forum User
New php-forum User
 
Posts: 50
Joined: Fri Jan 20, 2012 8:00 am


Return to PHP & MySQL Security

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron