protect against SQL injection: prepared statements versus formatted string

[arianhojat]

Hello all,

I asked this question 3 times in PHP Help forum but no one answered, so hoping maybe a MYSQL guru who uses php might know better...

Even though this is more php related, i dont want to post same topic over there again when someone over here might be more knowledgable...

 

 

Had a few questions 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?

what are advantages/dis to each if there are any and what do you prefer? (i know prepared statements are faster if doing many of same query and i beleive dont need to addslashes to the variables you pass it since it does that builtin.).

 

2. my $comments and $age variables can either have string(or int data respectively) but if those textfields pretend were filled blank by the 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 not like that 'NULL' is not an integer for example. so would this be a reason not to use sprintf? i guess you could always get passed it by doing...

sprintf

(

"UPDATE theDB.table SET Description=%s, Comments=%s, age=".( ($age==NULL)?'%s':'%d')." WHERE id=%d",

secure_query($description),

(($comments!="") ? secure_query($comments) : 'NULL' ),

(($age!="") ? secure_query($age) : 'NULL' ),

secure_query($id)

);

What would you do in this situation, use prepared statement instead?

 

 

Thanks in advance guys!

[bubblegum.anarchy]

The end result of either a `prepared query` or a `sprintf query` is exactly the same... a string... The formatting of the query string passed to mysql is what is important.

 

Quote strings and type define numbers so that there is not chance a quoted string will be passed as a number.

[btherl]

It's a matter of style really.. I use #2, but I don't use sprintf(), I just concatenate the query together placing the variables in place.

 

$powerpuff = mysql_real_escape_string($powerpuff);
$sql = 'SELECT foo '
. ' FROM bar '
. ' WHERE bubbles =\'' . $powerpuff_esc . '\'';

 

Something like that.. it's quite readable in an editor with highlighting.  I would go for another approach if highlighting was not available though, as it looks yucky without.

[arianhojat]

btherl,

yeh i use that method but maybe i try switching it up with prepared queries now for repeated INSERTs/other queries.