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