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

Groups > comp.lang.php > #10287

Re: is mysqli_real_escape_string bullet proof with binary data?

Cross-posted to 2 groups.

Show all headers | View raw

On 28-07-2013 18:01, Pierre Jaury wrote:
> Luuk <luuk@invalid.lan> writes:
>
>> On 28-07-2013 01:50, The Natural Philosopher wrote:
>>> On 27/07/13 21:59, Luuk wrote:
>>>> On 27-07-2013 22:25, 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).
>>>>>
>>>>> 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.
>>>>>
>>>>> Data sent to MySQL as bound parameters in prepared statements is
>>>>> shipped as is, it not escaped or processed in any way before the
>>>>> MySQL API structures are filled with it. Now, for your binary data
>>>>> to be handled as is by the MySQL server (and skip the charset
>>>>> processing), the parameter must be bound as MYSQL_TYPE_BLOB, which
>>>>> you may control when using bind_param.
>>>>>
>>>>> See http://fr2.php.net/manual/fr/mysqli-stmt.bind-param.php and the
>>>>> "b" (as in binary) flag for details.
>>>>>
>>>>
>>>> Ok, the code i posted earlier in this thread is also posted here:
>>>> http://pastebin.com/5D8ZGEhy
>>>>
>>>> Whn using 'bind_param' i choose to use 's' as in string, and not 'b'
>>>> for blob/binary.
>>>>
>>>> The code stores an exact (binary) copy of the file in a MySQL database.
>>>>
>>>> When the 's' in bind_param is changed to 'b', it does not store an
>>>> exact binary copy of the file....
>>>>
>>> Why not?
>>>
>>>> [root@test]> select id, left(hex(image),64) from testpng where id=1;
>>>> +----+------------------------------------------------------------------+
>>>> | id | left(hex(image),64)                                              |
>>>> +----+------------------------------------------------------------------+
>>>> |  1 | 89504E470D0A1A0A0000000D49484452000000090000000A0806000000660574 |
>>>> +----+------------------------------------------------------------------+
>>>> 1 row in set (0.00 sec)
>>>>
>>>> [root@test]> \! hexdump -C upArrow.png | head -2
>>>> 00000000  89 50 4e 47 0d 0a 1a 0a  00 00 00 0d 49 48 44 52
>>>> |.PNG........IHDR|
>>>> 00000010  00 00 00 09 00 00 00 0a  08 06 00 00 00 66 05 74
>>>> |.............f.t|
>>>> [root@test]>
>>>>
>>>> I'm not sure how
>>>> "Data sent to MySQL as bound parameters in prepared statements is
>>>> shipped as is, it not escaped or processed in any way before the
>>>> MySQL API structures are filled with it."
>>>> and
>>>> "Now, for your binary data to be handled as is by the MySQL server
>>>> (and skip the charset processing), the parameter must be bound as
>>>> MYSQL_TYPE_BLOB, which you may control when using bind_param."
>>>> relate to each other...
>>>>
>>> The meaning is clear: provided you tell the API what it is to expect, it
>>> will not be processed in ways that do not apply to the object type.
>>>
>>> But you contradict that, by saying that binding it as binary  does in
>>> fact get processed and only as a string will it be passed unaltered.
>>>
>>
>> I was not trying to contradict anything. I was reading the post (from
>> Pierre) and was under the impression that i SHOULD use 'b' in
>> bind_param.
>>
>> I was using 's' in bind_param, and my testprog works ok
>>
>> These 2 lines made /me confused ;)
>>
>
> Those two lines may sound contradictory, yet they are not. When sent by
> the client (wether PHP or any library using the MySQL C API), data is
> shipped as is using MySQL binary protocol after being flagged with a
> type constant and some other fields (see
> http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-data-structures.html
> for details).
>
> Nevertheless, depending on the type you sent data as and your column
> types, the MySQL server may process your request content. For instance,
> a string argument stored in a char column will go through the encoding
> process, based on the client connection encoding and column charset
> and collation.
>

ok, so i'm lucky that the encoding process did not change my png-file ;)

> I still feel weird about your example, I am guessing some of the
> client-side process your wrote is wrong, but I cannot figure what part.
>

thanks for that too ;)

it's a weird example, and of no use for any other thing than being an 
example ;)

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