CODEWITHSUNDEEP
phpmysqlprepared-statement
David Ducker
David Ducker

Reputation: 11

PHP\MYSQL prepared statements

I'm new to mysql and php and wanted to clarify something on prepared-statements.

All the examples I've seen only add params for the actual data in the table, is it bad practice to paramaterise all the fields so there are less statements? e.g. 

UPDATE ? SET ?=? WHERE ID=?

Thank you

Upvotes: 1

Views: 161

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157839

Although excellent deceze's answer covers your question literally, there is something else to mention.

If you take a look at any major freamework, you will find a function... update() which works pretty the same way:

function update($table, $data, $id) {

so, the approach is quite popular in general.
Nevertheless, it's wrong and misguided as well.

Being initially inflexible, it quickly become unusable:

  • once you need a field name other than conventional id,
  • once you need a more complex condition,
  • once you need a JOIN-based update,
  • once you need to use an SQL function(!) to process some data

all these cases will make initially small & neat function into bloated monster.

But the remedy is simple.
What you really need is a placeholder for the every data type that may be added to the query. So, welcome to SafeMysql - a library that will fulfill your dreams:

$db->query("UPDATE ?n SET ?u WHERE id=?i", $table, $data, $id);

it is doing exactly what you want, but doing it proper way. And even such a complex query won't make it any trouble:

$data = array('field'=>$value,'field2'=>$value);
$ids  = array(1,2,4);
$sql  = "UPDATE t SET ts=unix_timestamp(), ip=inet_aton(?s),?u WHERE id IN(?a)";
$db->query($sql, $ip, $data, $ids);

Though it's purpose not to make "less [SQL] statements", but to write less PHP code and make your query safe, while keeping it familiar conventional SQL, without trade-offs like learning new language of some Query Builder which is merely an SQL dialect written in PHP.

Upvotes: 1

deceze
deceze

Reputation: 522024

You cannot parameterize identifiers. You can only parameterize data. Otherwise the main point of parameterization, the separation between statement structure and data, is pretty moot. Understand that parameterization is not just fancy copy-and-paste, it's a technique to make sure the database has a clear separation between the static parts (which it will take its instructions from) and the dynamic parts (which it will treat as data and data only).

Personally I don't really understand why everyone tries to minimize SQL as much as possible and doesn't want to write any. In a real world application there are a defined, finite number of SQL queries that an application will send to the database. There's going to be one query to find a user by username, one to calculate the number of foos in the bar table etc. etc. Write those queries once, long-hand. Have a method findUserByName() somewhere which contains the query to find a user by name written out in SQL. You don't need to dynamically cobble together every single query, you just need to write it once. That also gives you a good "inventory" of all the possible database interaction and allows hand-tweaking where necessary. </rant>

So, it's both not possible and misguided.

Upvotes: 4

Artjom Kurapov
Artjom Kurapov

Reputation: 6155

Yes, obviously because it is not readable. Even with values, I would argue that it is better to have placeholders in key form. For example

UPDATE mytable SET `name`=:fullname WHERE ID=:id

Upvotes: 0

Related Questions