| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182329 is a reply to message #182327] |
Sun, 28 July 2013 16:01   |
Pierre Jaury
Messages: 5
Registered: July 2013
Karma:
|
Junior Member |
|
|
Luuk <luuk(at)invalid(dot)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(at)gmail(dot)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-struct ures.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.
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.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]