Path: csiph.com!usenet.pasdenom.info!goblin3!goblin2!goblin.stu.neva.ru!newsfeed.xs4all.nl!newsfeed4.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail Date: Sun, 28 Jul 2013 20:31:11 +0200 From: Luuk User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:17.0) Gecko/20130620 Thunderbird/17.0.7 MIME-Version: 1.0 Newsgroups: comp.lang.php,comp.databases.mysql Subject: Re: is mysqli_real_escape_string bullet proof with binary data? References: <874nbf6981.fsf@silissia.kaiyou.fr> <0vodca-nlt.ln1@luuk.invalid.lan> <8761vu4qtc.fsf@silissia.kaiyou.fr> In-Reply-To: <8761vu4qtc.fsf@silissia.kaiyou.fr> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Message-ID: Lines: 123 NNTP-Posting-Host: 2001:980:9089:1:b946:fa54:8785:43dd X-Trace: 1375036801 news.xs4all.nl 15996 [2001:980:9089:1:b946:fa54:8785:43dd]:48189 X-Complaints-To: abuse@xs4all.nl Xref: csiph.com comp.lang.php:10287 comp.databases.mysql:3949 On 28-07-2013 18:01, Pierre Jaury wrote: > Luuk 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 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 ;)