FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » is mysqli_real_escape_string bullet proof with binary data?
Show: Today's Messages :: Polls :: Message Navigator
Return to the default flat view Create a new topic Submit Reply
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 Go to previous messageGo to previous message
Pierre Jaury is currently offline  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.
[Message index]
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Major trouble with PhpDocumentor
Next Topic: Education Path to become a PHP developer using free online courses
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 03:26:58 GMT 2024

Total time taken to generate the page: 0.05181 seconds