CODEWITHSUNDEEP
phpmysqlsqlsecurityprepared-statement
Alberto Fontana
Alberto Fontana

Reputation: 948

I get data from db and reuse them in another query. Is it redundant to re-prepare them?

I always wondered if my behavior (ALWAYS preparing/escaping) is redundant or, for security reasons, always good to do. Here is an example:

FIRST STEP - A user insert some data

For instance, a user inserts a post. This post is saved, as written by the user, into the variable $post. Then i put it into DB

 $stmt = $dbh->prepare("INSERT INTO posts VALUES (:post)"); //simplified query
 $stmt->bindParam(":post", $post); //i prevent SQL injection
 $stmt->execute(); //run the query

As you can notice, i prevented SQL injection. Necessary, being $post written by users.

SECOND STEP - I fetch and reuse in another query

For a some reason i fetch that post

$stmt = $dbh->prepare("SELECT post FROM posts WHERE ..."); //simplified query
$stmt->execute();
$post=$stmt->fetch(PDO::FETCH_COLUMN,0);

Now, as before, i have the post saved into $post. The difference is, that post was taken from DB and not from user.

Here comes the question: if i'm gonna use the just fetched $post again in another query (INSERT, compare, UPDATE, etc...), should i prepare/escape it again? The logic says yes, because that value was escaped when it was inserted into DB, but being I unexpert about security, i wanna be sure...maybe by not escaping again, i'm giving some hackers a chance to attack my application!

//are these dangerous?
$stmt = $dbh->prepare("SELECT something FROM somewhere WHERE some_value=$post");
$stmt = $dbh->prepare("INSERT INTO somewhere VALUES ($post)");

//are these safe or simply redundant?
$stmt = $dbh->prepare("SELECT something FROM somewhere WHERE some_value=:post");
$stmt->bindParam(":post", $post); //i prevent SQL injection

$stmt = $dbh->prepare("INSERT INTO somewhere VALUES (:post)");
$stmt->bindParam(":post", $post); //i prevent SQL injection

Upvotes: 0

Views: 83

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20804

I believe that prepared statements are the php version of query parameters. That being the case, security is not the only benefit.

One other benefit is that special characters get escaped. The most common example is the apostrophe. You don't want apostrophe's being treated as single quotes. Bad things happen.

Another benefit is better performance, especially on busy sites. When your db first sees an sql string it has to compile it and develop an execution plan. The compilation stays cached for awhile. If you run the same query with a different parameter, it will use the cached compilation. If you sent in a different string, it would have to compile again.

Using query parameters, even when the data comes from your db, is always as good idea.

Upvotes: 1

Related Questions