options for Protecting against SQL Injection (Prepared Statements or sprintf?)

[arianhojat]

Had a few questions (4 specifically ) regarding sprintf versus prepared statements to protect a query from injection etc.

 

Here are my test examples that my questions are based off of...

 

##### sprintf example

<?php

function secure_query($var)//returns variable for database insertion
{
if (!is_numeric($var))//string
return "'". mysql_real_escape_string($var) ."'"; //basically adds slashes to strings, (heard this is betetr than addslashes() as its custom to mysql's current char set )
else	
return $var;//number	
}

$query =
sprintf
(
"UPDATE theDB.table SET Description=%s, Comments=%s, age=%i WHERE id=%d",
, secure_query($description), (($comments!="") ? secure_query($comments) : 'NULL' ), (($age!="") ? secure_query($age) : 'NULL' ), secure_query($id)
);

?>

 

 

#####prepared example

<?php

if ($stmt = $mysqli->prepare("UPDATE theDB.table SET Description=?, Comments=?, age=? WHERE id=?")) 
{

    $stmt->bind_param("s", $description );
    $stmt->bind_param("s", (($comments!="") ? $comments : 'NULL' ) );
    $stmt->bind_param("i", (($age!="") ? $age : 'NULL' ) );
    $stmt->bind_param("i", $id );

    $stmt->execute();
    $stmt->close();
} 

?>

 

 

1. Is a prepared statement in php safer than say a normal query with formatted with sprintf which is what i have currently?

what are advantages/dis to each if there are any (i know prepared statements are faster if doing many of same query and i beleive dont need to add slashes to the variables you pass it.).

 

2. I know you have to secure the query for sprintf. But do I need to use the secure_query function (which is basically a shortcut to do mysql_real_escape_string on text vars), when i do a prepared statement?, or a prepared statement is already secure and takes care of this somehow internally so i dont need to?

*** i think based on some research prepared statements take this into account, and hence i dont need to add slashes to any vars i pass it.

 

3. my $comments and $age variables can either have string/int data but if those textfields pretend werent filled out by user, i want to put NULL in the database.

Since I can pass an string/integer or 'NULL' to sprintf in those variables, it seems like sprintf will definately complain that 'NULL' is not an integer or convert that value to 0.

 

4. So is Prepared Statements the way to go? or certain situations when def use one over other.

 

 

I asked this question before but no one answered so giving this one more shot if anyone can help clear up things,

Thanks in advance guys!

[arianhojat]

bump, anyone can help?