Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #10261

Re: is mysqli_real_escape_string bullet proof with binary data?

Cross-posted to 2 groups.

Show all headers | View raw

[Multipart message — attachments visible in raw view] - view raw

Jerry Stuckle <jstucklex@attglobal.net> writes:

> On 7/27/2013 4:25 PM, Pierre Jaury wrote:
>> Denis McMahon <denismfmcmahon@gmail.com> writes:
>>
>>> On Sat, 27 Jul 2013 11:45:50 +0200, Luuk wrote:
>>>
>>>> On 27-07-2013 11:31, The Natural Philosopher wrote:
>>>
>>>>> The target is to create and store thumbnail PNG images in a Mysql
>>>>> table.
>>>
>>>> As far as the subject goes, i would say:
>>>> DO NOT TOUCH binary data.....
>>>> Simply store it, or not, in your database.....
>>>
>>> Is it trying to store binary data as character data, or does it think it
>>> needs to string escape binary data types?
>>
>> Escaping strings is a concept only useful when building request strings
>> that include user supplied data. It does avoid concatenating strings
>> whith special/control characters that may interfere with the original
>> language. There is no need to escape strings if you do not embed
>> them inside your request string (eg. if you use bound parameters).
>>
>
> Incorrect.  ALL strings, whether user-supplied or not, need to be
> escaped if you don't use bound values.

Well, mostly depends on what is meant by "user-supplied". I realize I
was not explicit enough, so: escaping strings is useful when crafting
interpreted code based on values that are not built in the local
scope, ie. parameters to your crafting process (which I mentionned and
will keep mentionning as user-supplied data, wether it is obtained from
a human operator, a file or basically any routine that is external to
the crafting scope).

>> This is true when dealing with SQL (write prepared statements without
>> including any user supplied data, then bind the parameters), bash
>> commands that only require one exec (use exec* instead of system), and
>> pretty much in any case you construct interpreted code at runtime.
>>
>
> Additional overhead, as I indicated before.  Additionally, binding
> numeric values greatly increases the overhead (because they do not
> need to be escaped when not using bound variables).  They just need to
> be validated as the appropriate numeric values (which needs to be done
> in either case).
>

There is, indeed, overhead due to the two-step model (preparation, then
execution). However, on a theorical basis, except that operational
overhead, preparing statements mostly have advantages :

* only the symbolic statement is sent as text, data can be shipped in a
proper binary form (which i believe MySQL does as part of a separate
query protocol),
* as a consequence, no need to build and encode strings on the client
side,
* no need to decode those strings on the server side, the server lexer
may process smaller strings,
* no need to escape text data at any time,
* data-independant statement optimizations may be cached.

Even if prepared statements sound theorically ideal, they still *do*
have a couple of conceptual drawbacks:

* replicating queries on multiple servers is much more complex (which
i believe mysql works around by building fully formatted queries sent to
binary logs),
* logging queries suffers the same complexity (requires that data is
encoded and a full text or binary query built on the server side),
* obviously many other related issues.

I am not really into benchmarks and I did not write applications big
enough based on recent MySQL versions to provide some actual feedback, I
suspect however that MySQL imlementation of prepared statements is still
sketchy (especially on the logging, replication and caching sides,
apparently). Maybe performance will improve with future versions of the
statement engine.

I would really like to read some real-world performance feedback on the
matter, if you have some interesting links to share :)

> A little knowledge is a dangerous thing.

I do not feel in a trolling mood, please stop these.

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Re: is mysqli_real_escape_string bullet proof with binary data? Denis McMahon <denismfmcmahon@gmail.com> - 2013-07-27 18:38 +0000
  Re: is mysqli_real_escape_string bullet proof with binary data? Pierre Jaury <pierre@jaury.eu> - 2013-07-27 22:25 +0200
    Re: is mysqli_real_escape_string bullet proof with binary data? Luuk <luuk@invalid.lan> - 2013-07-27 22:59 +0200
      Re: is mysqli_real_escape_string bullet proof with binary data? The Natural Philosopher <tnp@invalid.invalid> - 2013-07-28 00:50 +0100
        Re: is mysqli_real_escape_string bullet proof with binary data? Luuk <luuk@invalid.lan> - 2013-07-28 17:44 +0200
          Re: is mysqli_real_escape_string bullet proof with binary data? Pierre Jaury <pierre@jaury.eu> - 2013-07-28 18:01 +0200
            Re: is mysqli_real_escape_string bullet proof with binary data? The Natural Philosopher <tnp@invalid.invalid> - 2013-07-28 17:43 +0100
            Re: is mysqli_real_escape_string bullet proof with binary data? Luuk <luuk@invalid.lan> - 2013-07-28 20:31 +0200
          Re: is mysqli_real_escape_string bullet proof with binary data? The Natural Philosopher <tnp@invalid.invalid> - 2013-07-28 17:39 +0100
            Re: is mysqli_real_escape_string bullet proof with binary data? Pierre Jaury <pierre@jaury.eu> - 2013-07-28 19:56 +0200
              Re: is mysqli_real_escape_string bullet proof with binary data? The Natural Philosopher <tnp@invalid.invalid> - 2013-07-28 19:14 +0100
                Re: is mysqli_real_escape_string bullet proof with binary data? Jerry Stuckle <jstucklex@attglobal.net> - 2013-07-28 15:10 -0400
            Re: is mysqli_real_escape_string bullet proof with binary data? Norman Peelman <npeelman@cfl.rr.com> - 2013-07-28 21:13 -0400
    Re: is mysqli_real_escape_string bullet proof with binary data? The Natural Philosopher <tnp@invalid.invalid> - 2013-07-28 00:46 +0100
    Re: is mysqli_real_escape_string bullet proof with binary data? Jerry Stuckle <jstucklex@attglobal.net> - 2013-07-27 19:56 -0400
      Re: is mysqli_real_escape_string bullet proof with binary data? Pierre Jaury <pierre@jaury.eu> - 2013-07-28 03:55 +0200
        Re: is mysqli_real_escape_string bullet proof with binary data? Jerry Stuckle <jstucklex@attglobal.net> - 2013-07-27 22:11 -0400
  Re: is mysqli_real_escape_string bullet proof with binary data? Jerry Stuckle <jstucklex@attglobal.net> - 2013-07-27 19:58 -0400

csiph-web