PDO when prepared statements and when not

[helenp]

Hi, I am trying to not use prepared statements where they are not needed.
As per what I read prepared statements should be used when using external data.

Now my question is what is external data?

If passed with a form of course I need prepared statements,
but I suppose if I use $number = $dbh->lastInsertId();
and use $number to insert the id I dont need prepared statements as the var is generated inside the page.

But what about sessions?
I have forms wich I send by post to itself using <?php echo $_SERVER['SCRIPT_NAME'];?>, then I store the values of the inputs in a session, and on next coming pages I use the values in the session storing the values in vars, and insert using the vars.
I suppose prepared statements should be used there.

But what about parameters generated on the page but stored in the session?
When a booking is done an Id is created, I select the LastInsertId and store it in the session,
like this: $_SESSION['bookings']['step4']['number'] = $number;
on other pages I use $number to check if booking already exist and if so delete it.
Should prepared statements be used there?
Thanks,

[swa66]

The use of prepared statements is to separate data from code in sql.

Why is that important: to avoid the data getting parsed by the database as code.
(which is what SQLinjection is all about, but it also prevents lesser errors too.

With PDO that means you have to be careful that it isn't going to switch to client side "pseudo" prepared statements as the data will get mixed in (escaped) into the code anyway.

Security thinks about a "layered" model where you have more than one layer of defense and if one fails it's no big deal as you have more protections in place.
Think of it as a "belt and suspenders" approach and always use prepared statements even if you do not really have to.
Moreover it makes it easier in the end as you only need to know how to do prepared statements anymore.
(honestly I've never done any non-prepared statement in mysqli - I woudn't even know where to begin without looking it up.

[helenp]

Thanks,
I am doing both as I read that prepared statements takes more resoureces as the conexion to mysql is done twice,
and I read this: "ou use a prepared statement whenever you have to pass external values to a query (like in this case). When it's just a constant query, you can use query()."

Queries is very easy, its just:

$result = $dbh->query("SELECT idWHERE statement = 'unpaid'
AND orderdatetime < ADDDATE( NOW( ) , INTERVAL -30
MINUTE )");
foreach ($result as $row) { echo $row[id];

And I learnt to do this wich is very short and suppose can be used if the vars is not external:

$sql2 = $dbh->exec ("UPDATE orders SET booked='deleted' WHERE id = '$id'");

Therefore my question when I can use the short one with exec

[topr8]

personally i use prepared statements with parameters, always. i feel the security element outweighs any slight processing one. you can also speed things up considerably by using multiple statements (maybe you are doing that already)

aside ... i use mysqli rather than pdo but i the same applies, i also only call stored procedures (using prepared statements) from web pages (absolutely no ad hoc queries allowed) and the web user only has permission to execute stored procedures, nothing else at all ... it effectively eliminates the chance of any kind of injection attack and limits the damage to virtually nothing should one happen.

[swa66]

as I read that prepared statements takes more resoureces as the conexion to mysql is done twice,

The connection is not made twice for prepared statements. That's false.
Your TCP connection remains in place.

There's a packet exchange in the existing connection extra and on a (very) slow connection or with a (very) high number of interactions you can measure a difference. But if you are reusing the same query over you'll in fact gain speed using prepared statements.

In real life you're unlikely to see any difference that's worth the effort and risk to use both.

[helenp]

I dont agree, however maybe its easier to only use prepare when parameter are used, however I have pages with very complex and long queries and with many queries, not equal, all diferent. So I think I should optimize them as the pages are very heavy using mysql queries.
However in select I dont see any use of using prepare while there is no parameter to bind.
This:

$result = $dbh->query("SELECT idWHERE statement = 'unpaid'
AND orderdatetime < ADDDATE( NOW( ) , INTERVAL -30
MINUTE )");
foreach ($result as $row) { echo $row[id]; 

You would do like this?

$result = $dbh->prepare("SELECT idWHERE statement = 'unpaid'
AND orderdatetime < ADDDATE( NOW( ) , INTERVAL -30
MINUTE )");
foreach ($result as $row) { echo $row[id]; 

Dont see the need of prepare