sprintf or prepared statement to protect Query from injection, etc?

[arianhojat]

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

 

For Querys (SELECT, UPDATE, INSERT, DELETE)...

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 have an example of each below in how i think i would use them based on examples i read (please correct if i am wrong somewhere).

 

##### 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 WHERE id=%d",
, secure_query($description), (($comments!="") ? secure_query($comments) : 'NULL' ), $id
);

?>

 

 

#####prepared example

 

I never have done a prepared statement in php so I had a couple questions here...

2. Do I need to use my secure_query function (which is basically a shortcut for mysql_real_escape_string), 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?

 

3. my $comments var can either have string data to insert or if the variable is empty i want to put NULL in the database.

So does sprintf's "%s" and prepared statement's "s" (tells functions to format/look for string data) realize that "'a comment'" or 'NULL' which i would possibly give those fuctions are both strings and hence valid, or will they bug out at 'NULL' since once its actually substituted into the query, it is NULL and hence not a string?

 

<?php

function secure_query($var)//returns variable for database insertion
{
if (!is_numeric($var))//string
return "'". mysql_real_escape_string($var) ."'";
else	
return $var;//number	
}


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

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

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

?>

 

[arianhojat]

whoops double posted.

[arianhojat]

whoops double posted (correction: triple posted, heh doh! i feel retarted. wish there was a way to at least delete the last comment in thread if its yours.). anyway here are my questions without code mixed in...

 

1. For Querys (SELECT, UPDATE, INSERT, DELETE)...

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 have an example of each below in how i think i would use them based on examples i read (please correct if i am wrong somewhere).

 

2. Do I need to use my secure_query function (which is basically a shortcut for mysql_real_escape_string), 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?

 

3. my $comments var can either have string data to insert or if the variable is empty i want to put NULL in the database.

So does sprintf's "%s" and prepared statement's "s" (tells functions to format/look for string data) realize that "'a comment'" or 'NULL' which i would possibly give those fuctions are both strings and hence valid, or will they bug out at 'NULL' since once its actually substituted into the query, it is NULL and hence not a string?

 

[arianhojat]

bump .

[arianhojat]

thinking about it more, prepared statements seem like better way to go?

 

for example if an integer field can also accept NULL values,

wont this screw up if using the sprintf method?

 

$query =

sprintf

(

"UPDATE theDB.raceStats SET lapsRun=%d, WHERE userid=%d",

, 'NULL', $id

);

[arianhojat]

last bump attempt be4 start new post.

hopefully someone out there knows answer.