0

Until now, I hapily lived with the escaping functions and the paradigm that clients communicates with the server using just sql commands:

select * from table where name = 'O\'Hara';

In the case of PDO, where you just create some template and feed it with different data, this paradigm is very likely broken. If not, the PDO would have to just call the escaping function itself, and there would really be no reason to use it (wouldn't be any different from escaping it yourself).

So, if the SQL communication paradigm between client and server is broken, how does it work? Are the queries no more send as SQL commands? Is it some extension of MySQL protocol? Is the MySQL protocol far more rich than just SQL conversation? Does it work in general, e.g. using remote server? Are there any limits for this feature compared to bare SQL commanding?

I have tried going through the mysql docs but haven't found anything relevant.

Tomas
  • 57,621
  • 49
  • 238
  • 373
  • 1
    http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks/8265319#8265319 – Your Common Sense Jan 24 '14 at 21:24
  • @YourCommonSense so what? Can you cite any particular paragraph from that answer? – Tomas Jan 24 '14 at 21:36
  • 1
    Quite a lot. But judging by tone, I doubt it would worth the trouble. – Your Common Sense Jan 24 '14 at 21:48
  • 1
    @YourCommonSense what tone? I read your answer you linked and didn't see answer to my question, so I'm asking. If you don't cite anything particular, perhaps it wasn't worth to post the first comment as well :-) – Tomas Jan 24 '14 at 21:54

1 Answers1

2

Therory:

Prepared statements are indeed a feature on protocol level, meaning the client sends the query template first and the parameters in second request. Also the client can send multiple data requests for the same - already prepared - query. (That's why the name)

Reality:

The PDO code is written in a way, that it not utilizes the protocol feature, it really just escapes the values and replaces the placeholders in the statement with that escaped values and then sends a regular SQL query to the server.

hek2mgl
  • 152,036
  • 28
  • 249
  • 266
  • 1
    Well, that's a big disappointment! Then why are PDOs so much promoted? I can stay then in my good old escaping world with no difference whatsoever. PS: can you please cite some resources? – Tomas Jan 24 '14 at 20:47
  • 1
    @Tomas PDO introduces some level of database abstraction, that, if you keep compatible to SQL standards, can help to run your application on several types of databases. Also I think even the - client site - prepared statements make your code looking better + having params and query separated in your application offers you serveral design possibilities – hek2mgl Jan 24 '14 at 20:50
  • Well, that doesn't seem like a real reason to turn one's code upside down :) You can separate params and query and have good looking code without PDO; that's just a design issue :) – Tomas Jan 24 '14 at 20:56
  • @Tomas I must admit that I'm not 100% safe with this topic. There are some rumors that some drivers support *real, server-side* prepared statements. I guess this depends on how the PDO driver is linked against libmysqlclient. I never really debugged this, but it's on my list ;). In any case you should try to disable `PDO::ATTR_EMULATE_PREPARES` and check what's happening using wireshark. If real prepared statements are used you can see the additional mysql request. Here you can see another + of PDO, you can change a lot under the hood, without touching the application – hek2mgl Jan 24 '14 at 21:01