| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182303 is a reply to message #182300] |
Sat, 27 July 2013 23:50   |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma:
|
Senior Member |
|
|
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'd really like to know why..
> But I'm stuck in colder weather,
> Maybe tomorrow will be better
>
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to lead are elected by the least capable of producing, and where the members of society least likely to sustain themselves or succeed, are rewarded with goods and services paid for by the confiscated wealth of a diminishing number of producers.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]