| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182300 is a reply to message #182299] |
Sat, 27 July 2013 20:59   |
Luuk
Messages: 329
Registered: September 2010
Karma:
|
Senior Member |
|
|
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....
[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...
But I'm stuck in colder weather,
Maybe tomorrow will be better
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]