7

What's exactly the difference between the two kinds of prepared statements ?

I think real prepared statements require server side support wich accepts paramenters after parsing and compiling the schema/template of sql code, and , I suppose ,that's what guarantees us against sql-injection.

In the case of emulated prepared statements ,with no server support, what does
it guarantee us against it ?

GionJh
  • 2,742
  • 2
  • 29
  • 68

2 Answers2

7

You are correct, real prepared statements must be supported by the server. A real prepared means querying the database in two steps.

The fist step consists in sending a query template, that the server can pre-compile. The database engine also prepares in advance the execution plan (mostly, what indexes will be used to serve the actual query).

The second step is giving actual values to the placeholders and run the actual query with these parameters.

This typically allows faster execution of several similar queries, because 1. the query has already been pre-compiled (the execution plan is already computed) and 2. only the parameters values are sent subsequently.

A emulated query is just a syntactic sugar, that only allows easier sending (not faster execution) of several, successive, similar queries. Full SQL statements are sent to the server everytime an emulated query is executed.

When the sever does not support real prepared statements, it is still recommended to use emulated prepared statements, because the driver still takes care of escaping values for you, making SQL injection less likely.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Don't call it "typically faster". The difference can be hardly noticeable for the many kind of queries. Not to mention that in many applications you just have no chance to fire it twice. – Your Common Sense Jul 28 '13 at 14:51
  • 1
    So in the case of real prepared statements we are guaranteed against injection, while in the case of emualated ones, the functions we use will try their best to internally escape characters in a secure way , is that right ? – GionJh Jul 28 '13 at 14:58
  • @user986437 in case of emulated ones we are guaranteed as well – Your Common Sense Jul 28 '13 at 15:00
  • @YourCommonSense why ? that's what I'm curious to know,in my understanding with real ps server support guarantees us, what does it guarantee us in absence of server support ? – GionJh Jul 28 '13 at 15:07
  • Is that a typo or are you really recommending emulated prepared statements even when real statements are available? If so, why? – deceze Jul 28 '13 at 17:03
  • 1
    @deceze No, my phrasing was ambiguous to say the least. I only meant: emulated prepared statements are *still useful* even if the server does not support real prepared statements. I have rephrased. – RandomSeed Jul 28 '13 at 20:42
  • @YourCommonSense Many ORM's are sufficiently badly written (or misused enough) so that several similar queries are executed in cascade (e.g. retrieving 10 items will often be done in 10 queries). But yes, the benefit in terms of performance is often negligible unless queries begin to be seriously complicated. – RandomSeed Jul 28 '13 at 20:46
  • 1
    And [this answer](http://stackoverflow.com/a/134138/1446005) nicely explains why prepared statements are not (always) sufficient to protect you from SQL injection. – RandomSeed Jul 28 '13 at 20:50
  • @RandomSeed this outdated answer nicely explains why prepared statements are not sufficient to protect you from SQL injection **when are not used**. Means they are sufficient when used. As simple as that. – Your Common Sense Jul 29 '13 at 04:56
2

In the case of emulated prepared statements ,with no server support, what does it guarantee us against it ?

That's a good question.

Ironically, the very same mechanism as with native prepared statements, in a way.

The main idea of a prepared statement is to make data literal processed properly and make such a processing inevitable. While details of such a processing is not that important.

The main problem with SQL injection is that it doesn't exist on it's own. All the "danger" is coming from improperly formatted query only. Every time you see an injection case, you will find an improperly formatted literal as a cause.

While properly formatted query is already protected.

that's why one should take care of no injections at all, but on the proper formatting only.
So prepared statement does such a formatting, this way or another.

That's all.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • please help me make clear one more point, you mean that with emulated ps declaring where the dangers in the code may reside and separating it from the safe code lets anti sql injections procedure filter those danger parts properly so that we are guaranteed ? – GionJh Jul 28 '13 at 15:57
  • there are no "danger" parts, and thus nothing to filter out as well. – Your Common Sense Jul 28 '13 at 16:04
  • 2
    The main idea behind prepared statements is efficiency in cases where multiple statements only differ in the use of different literal values. In such cases the statement does only need to be parsed once and can be executed multiple times with different values. Protection against SQL injections is just a side-effect as prepared statement and values are separated from each other. However, you can still have SQL injections when including user defined values into the statement that is to be prepared (happens all too often). – Gumbo Jul 28 '13 at 18:10
  • This one but a delusion. – Your Common Sense Jul 28 '13 at 18:12