parametrized sql?

[iam4423]

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

[egami]

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.

[minimihi]

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]

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

[minimihi]

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

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]

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

[minimihi]

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
At least I successfully made you waste some time sorry about that

[iam4423]

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]

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