parametrized sql?

A

Anonymous

Guest
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: 
$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
 
PHP: 
$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
PHP: 
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...

PHP: 
$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.
 
PHP: 
$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:
PHP: 
$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
 
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
 
how do i check the value types in query? i just dont understand how the array gets put into the query?

php
Code: 
$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: 
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
 
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
 
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
 
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: 
$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
 
You must log in or register to reply here.
Back
Top