| is mysqli_real_escape_string bullet proof with binary data? [message #182274] |
Sat, 27 July 2013 09:31  |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
The target is to create and store thumbnail PNG images in a Mysql table.
Also any tips on actually getting the thumbnail data into a variable -
which package is recommended? I've always used GD, but never been 100%
happy with it
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182275 is a reply to message #182274] |
Sat, 27 July 2013 09:45  |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27-07-2013 11:31, The Natural Philosopher wrote:
> The target is to create and store thumbnail PNG images in a Mysql table.
> Also any tips on actually getting the thumbnail data into a variable -
> which package is recommended? I've always used GD, but never been 100%
> happy with it
>
As far as the subject goes, i would say:
DO NOT TOUCH binary data.....
Simply store it, or not, in your database.....
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182277 is a reply to message #182275] |
Sat, 27 July 2013 11:08 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/07/13 10:45, 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.
>> Also any tips on actually getting the thumbnail data into a variable -
>> which package is recommended? I've always used GD, but never been 100%
>> happy with it
>>
>
> As far as the subject goes, i would say:
> DO NOT TOUCH binary data.....
>
> Simply store it, or not, in your database.....
Well exactly. the question is how to get it in there reliably using the
PHP API.
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182278 is a reply to message #182277] |
Sat, 27 July 2013 13:28 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27-07-2013 13:08, The Natural Philosopher wrote:
> On 27/07/13 10:45, 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.
>>> Also any tips on actually getting the thumbnail data into a variable -
>>> which package is recommended? I've always used GD, but never been 100%
>>> happy with it
>>>
>>
>> As far as the subject goes, i would say:
>> DO NOT TOUCH binary data.....
>>
>> Simply store it, or not, in your database.....
> Well exactly. the question is how to get it in there reliably using the
> PHP API.
>
>
When reading file contents, i see no need to do a mysqli_real_escape.
<?php
$link = mysqli_connect("localhost","test","test","test") or
die("Error " . mysqli_error($link));
$query = "CREATE TABLE `testpng` ( `id` int(11) NOT NULL
AUTO_INCREMENT, `image` blob, PRIMARY KEY (`id`))";
$result = $link->query($query);
if (!$result) { echo "Could not create table....\n"; exit; } else {
$stmt = $link->prepare("INSERT INTO testpng (image) VALUES (?)");
$file = file_get_contents('upArrow.png');
echo "Size before: ".strlen($file)."<br>\n";
$stmt->bind_param('s', $file);
$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
$stmt->close();
$stmt = $link->query("SELECT image FROM testpng;");
$row = $stmt->fetch_object();
$file = $row->image;
echo "Size before: ".strlen($file)."<br>\n";
}
?>
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182279 is a reply to message #182274] |
Sat, 27 July 2013 13:35 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 07/27/2013 05:31 AM, The Natural Philosopher wrote:
> The target is to create and store thumbnail PNG images in a Mysql table.
> Also any tips on actually getting the thumbnail data into a variable -
> which package is recommended? I've always used GD, but never been 100%
> happy with it
>
Well, you don't sat why you don't like GD but that is what I use (on
one of my sites) so here is what I do---
When writing *to* the database:
A) You have sql LOAD_FILE to store a file (filesystem) in the db.
I write the GD image resource to a tmp file and then store it using
LOAD_FILE.
imagepng($img, $destfile);
/* stuff */
$query = "UPDATE table SET img_data = LOAD_FILE('$destfile') WHERE ?"
B) ??
When retrieving *from* the db:
A) The binary data is in the result set such that:
header("Content-Type: image/png");
echo $result['image_data']
-or-
You can create a GD image resource by using:
$img = imagecreatefromstring($result['image_data']);
/* GD manipulations */
header("Content-Type: image/png");
imagepng($img);
either should output an image to the browser.
B) output_buffering lets you do some crazy stuff.
-or-
Take a look at the Imagick module for PHP (PECL).
http://www.php.net/manual/en/imagick.getimageblob.php
http://www.php.net/manual/en/imagick.readimageblob.php
http://www.php.net/manual/en/imagick.readimagefile.php
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182281 is a reply to message #182275] |
Sat, 27 July 2013 13:52 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 5:45 AM, 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.
>> Also any tips on actually getting the thumbnail data into a variable -
>> which package is recommended? I've always used GD, but never been 100%
>> happy with it
>>
>
> As far as the subject goes, i would say:
> DO NOT TOUCH binary data.....
>
> Simply store it, or not, in your database.....
Bull. Binary data works fine in a database - and storing it there has
many advantages. Easy backup and improved data integrity are just two
of them.
A file system is just a database with a different means of access. They
both store bytes.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182282 is a reply to message #182279] |
Sat, 27 July 2013 13:56 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 9:35 AM, Norman Peelman wrote:
> On 07/27/2013 05:31 AM, The Natural Philosopher wrote:
>> The target is to create and store thumbnail PNG images in a Mysql table.
>> Also any tips on actually getting the thumbnail data into a variable -
>> which package is recommended? I've always used GD, but never been 100%
>> happy with it
>>
>
> Well, you don't sat why you don't like GD but that is what I use (on
> one of my sites) so here is what I do---
>
> When writing *to* the database:
> A) You have sql LOAD_FILE to store a file (filesystem) in the db.
>
> I write the GD image resource to a tmp file and then store it using
> LOAD_FILE.
>
> imagepng($img, $destfile);
> /* stuff */
> $query = "UPDATE table SET img_data = LOAD_FILE('$destfile') WHERE ?"
>
No, you do NOT have to write the image to a temp file and use LOAD FILE.
It can be written directly from the binary image in memory.
> B) ??
>
>
> When retrieving *from* the db:
> A) The binary data is in the result set such that:
>
> header("Content-Type: image/png");
> echo $result['image_data']
>
> -or-
>
> You can create a GD image resource by using:
>
> $img = imagecreatefromstring($result['image_data']);
> /* GD manipulations */
> header("Content-Type: image/png");
> imagepng($img);
>
> either should output an image to the browser.
>
> B) output_buffering lets you do some crazy stuff.
>
>
> -or-
>
> Take a look at the Imagick module for PHP (PECL).
>
> http://www.php.net/manual/en/imagick.getimageblob.php
> http://www.php.net/manual/en/imagick.readimageblob.php
> http://www.php.net/manual/en/imagick.readimagefile.php
>
Yes, the GD functions work, but imagick is much more flexible.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182283 is a reply to message #182274] |
Sat, 27 July 2013 13:57 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 5:31 AM, The Natural Philosopher wrote:
> The target is to create and store thumbnail PNG images in a Mysql table.
> Also any tips on actually getting the thumbnail data into a variable -
> which package is recommended? I've always used GD, but never been 100%
> happy with it
>
Pretty easy to do. But I know you don't want to hear from me, so I
won't bother telling you the right way to do it.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182285 is a reply to message #182279] |
Sat, 27 July 2013 14:33 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/07/13 14:35, Norman Peelman wrote:
> On 07/27/2013 05:31 AM, The Natural Philosopher wrote:
>> The target is to create and store thumbnail PNG images in a Mysql table.
>> Also any tips on actually getting the thumbnail data into a variable -
>> which package is recommended? I've always used GD, but never been 100%
>> happy with it
>>
>
> Well, you don't sat why you don't like GD but that is what I use (on
> one of my sites) so here is what I do---
>
> When writing *to* the database:
> A) You have sql LOAD_FILE to store a file (filesystem) in the db.
>
> I write the GD image resource to a tmp file and then store it using
> LOAD_FILE.
>
> imagepng($img, $destfile);
> /* stuff */
> $query = "UPDATE table SET img_data = LOAD_FILE('$destfile') WHERE ?"
>
that's what I have used in the past..
> B) ??
>
>
> When retrieving *from* the db:
> A) The binary data is in the result set such that:
>
> header("Content-Type: image/png");
> echo $result['image_data']
>
> -or-
>
> You can create a GD image resource by using:
>
> $img = imagecreatefromstring($result['image_data']);
> /* GD manipulations */
> header("Content-Type: image/png");
> imagepng($img);
>
> either should output an image to the browser.
>
> B) output_buffering lets you do some crazy stuff.
>
>
> -or-
>
> Take a look at the Imagick module for PHP (PECL).
>
I will. TIA
> http://www.php.net/manual/en/imagick.getimageblob.php
> http://www.php.net/manual/en/imagick.readimageblob.php
> http://www.php.net/manual/en/imagick.readimagefile.php
>
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182286 is a reply to message #182284] |
Sat, 27 July 2013 14:36 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 9:58 AM, Luuk wrote:
> On 27-07-2013 15:52, Jerry Stuckle wrote:
>>> Simply store it, or not, in your database.....
>>
>> Bull.
>
> I did not know that reading could be so hard... ;)
It's not. But
"DO NOT TOUCH binary data....."
Is completely incorrect. Like all strings, it MUST be processed with
mysql_real_escape_string() to get the correct data in the database.
However, I see that part got cut off in my reply. Guess I shouldn't be
trying to carry on a discussion with the wife while posting.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182287 is a reply to message #182278] |
Sat, 27 July 2013 14:36 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/07/13 14:28, Luuk wrote:
> On 27-07-2013 13:08, The Natural Philosopher wrote:
>> On 27/07/13 10:45, 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.
>>>> Also any tips on actually getting the thumbnail data into a variable -
>>>> which package is recommended? I've always used GD, but never been 100%
>>>> happy with it
>>>>
>>>
>>> As far as the subject goes, i would say:
>>> DO NOT TOUCH binary data.....
>>>
>>> Simply store it, or not, in your database.....
>> Well exactly. the question is how to get it in there reliably using the
>> PHP API.
>>
>>
>
>
> When reading file contents, i see no need to do a mysqli_real_escape.
>
> <?php
> $link = mysqli_connect("localhost","test","test","test") or
> die("Error " . mysqli_error($link));
>
> $query = "CREATE TABLE `testpng` ( `id` int(11) NOT NULL
> AUTO_INCREMENT, `image` blob, PRIMARY KEY (`id`))";
> $result = $link->query($query);
>
> if (!$result) { echo "Could not create table....\n"; exit; } else {
> $stmt = $link->prepare("INSERT INTO testpng (image) VALUES (?)");
> $file = file_get_contents('upArrow.png');
> echo "Size before: ".strlen($file)."<br>\n";
> $stmt->bind_param('s', $file);
> $stmt->execute();
> printf("%d Row inserted.\n", $stmt->affected_rows);
> $stmt->close();
>
> $stmt = $link->query("SELECT image FROM testpng;");
> $row = $stmt->fetch_object();
> $file = $row->image;
> echo "Size before: ".strlen($file)."<br>\n";
> }
> ?>
...and hope that the file 'upArrow.png' does not contain a ['] or three?
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182288 is a reply to message #182286] |
Sat, 27 July 2013 14:43 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 10:36 AM, Jerry Stuckle wrote:
> On 7/27/2013 9:58 AM, Luuk wrote:
>> On 27-07-2013 15:52, Jerry Stuckle wrote:
>>>> Simply store it, or not, in your database.....
>>>
>>> Bull.
>>
>> I did not know that reading could be so hard... ;)
>
> It's not. But
>
> "DO NOT TOUCH binary data....."
>
> Is completely incorrect. Like all strings, it MUST be processed with
> mysql_real_escape_string() to get the correct data in the database.
>
> However, I see that part got cut off in my reply. Guess I shouldn't be
> trying to carry on a discussion with the wife while posting.
>
I should add you can use bound parameters instead - but beware that
bound parameters create additional overhead.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182293 is a reply to message #182287] |
Sat, 27 July 2013 15:22 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27-07-2013 16:36, The Natural Philosopher wrote:
> On 27/07/13 14:28, Luuk wrote:
>> On 27-07-2013 13:08, The Natural Philosopher wrote:
>>> On 27/07/13 10:45, 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.
>>>> > Also any tips on actually getting the thumbnail data into a variable -
>>>> > which package is recommended? I've always used GD, but never been 100%
>>>> > happy with it
>>>> >
>>>>
>>>> As far as the subject goes, i would say:
>>>> DO NOT TOUCH binary data.....
>>>>
>>>> Simply store it, or not, in your database.....
>>> Well exactly. the question is how to get it in there reliably using the
>>> PHP API.
>>>
>>>
>>
>>
>> When reading file contents, i see no need to do a mysqli_real_escape.
>>
>> <?php
>> $link = mysqli_connect("localhost","test","test","test") or
>> die("Error " . mysqli_error($link));
>>
>> $query = "CREATE TABLE `testpng` ( `id` int(11) NOT NULL
>> AUTO_INCREMENT, `image` blob, PRIMARY KEY (`id`))";
>> $result = $link->query($query);
>>
>> if (!$result) { echo "Could not create table....\n"; exit; } else {
>> $stmt = $link->prepare("INSERT INTO testpng (image) VALUES (?)");
>> $file = file_get_contents('upArrow.png');
>> echo "Size before: ".strlen($file)."<br>\n";
>> $stmt->bind_param('s', $file);
>> $stmt->execute();
>> printf("%d Row inserted.\n", $stmt->affected_rows);
>> $stmt->close();
>>
>> $stmt = $link->query("SELECT image FROM testpng;");
>> $row = $stmt->fetch_object();
>> $file = $row->image;
>> echo "Size before: ".strlen($file)."<br>\n";
>> }
>> ?>
> ..and hope that the file 'upArrow.png' does not contain a ['] or three?
>
luuk@opensuse:~/public_html/temp> grep "'" upArrow.png
Binary file upArrow.png matches
luuk@opensuse:~/public_html/temp> hexdump -C upArrow.png | grep "'"
00000110 53 00 a0 04 00 60 cb 63 62 e3 00 50 2d 00 60 27
|S....`.cb..P-.`'|
00000260 00 b0 6a 3e 01 7b 91 2d a8 5d 63 03 f6 4b 27 10
|..j>.{.-.]c..K'.|
000003a0 51 c2 27 22 93 a8 4b b4 26 ba 11 f9 c4 18 62 32
|Q.'"..K.&.....b2|
000003c0 37 24 12 89 43 32 27 b9 90 02 49 b1 a4 54 d2 12
|7$..C2'...I..T..|
00000530 fb 81 0e 41 c7 4a 27 5c 27 47 67 8f ce 05 9d e7
|...A.J'\'Gg.....|
00000720 0e 85 50 7e e8 d6 d0 07 61 e6 61 8b c3 7e 0c 27
|..P~....a.a..~.'|
000007c0 91 bc 35 79 24 c5 33 a5 2c e5 b9 84 27 a9 90 bc
|..5y$.3.,...'...|
000009e0 dd bd f3 7a 6f f7 c5 f7 f5 df 16 dd 7e 72 27 fd
|...zo.......~r'.|
000009f0 ce cb bb d9 77 27 ee ad bc 4f bc 5f f4 40 ed 41
|....w'...O._.@.A|
Yes it does, but who cares?
Are you really that afraid for code-insertion, that you fail to know
that this cannot go wrong?
Or can you explain me how i can kill my database with this?
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182294 is a reply to message #182293] |
Sat, 27 July 2013 15:34 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
Even if i add the following function:
function fwrite_stream($fp, $string) {
for ($written = 0; $written < strlen($string); $written += $fwrite) {
$fwrite = fwrite($fp, substr($string, $written));
if ($fwrite === false) {
return $written;
}
}
return $written;
}
and at the end of my code:
$fp = fopen('NewUpArrow.png','wb');
fwrite_stream($fp, $file);
Then i can see that i created a copy of the original file:
luuk@opensuse:~> diff -s upArrow.png NewUpArrow.png
Files upArrow.png and NewUpArrow.png are identical
|
|
|
|
| I've always used GD, but never been 100% happy with it (Was: is mysqli_real_escape_string bullet proof with binary data?) [message #182296 is a reply to message #182274] |
Sat, 27 July 2013 16:09 |
J.O. Aho
Messages: 194
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/07/13 11:31, The Natural Philosopher wrote:
> The target is to create and store thumbnail PNG images in a Mysql table.
> Also any tips on actually getting the thumbnail data into a variable -
> which package is recommended? I've always used GD, but never been 100%
> happy with it
If you ain't happy with GD, then use ImageMagick, if possible use the
php extension and not the binaries with exec() which quite many seems to
do. Using the extension you will notice it's far faster than using the
binaries.
www.php.net/manual/en/book.imagick.php
--
//Aho
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182297 is a reply to message #182281] |
Sat, 27 July 2013 18:10 |
Pierre Jaury
Messages: 5
Registered: July 2013
Karma: 0
|
Junior Member |
|
|
Jerry Stuckle <jstucklex(at)attglobal(dot)net> writes:
> On 7/27/2013 5:45 AM, 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. Also any tips on actually getting the thumbnail data into a
>>> variable - which package is recommended? I've always used GD, but
>>> never been 100% happy with it
>>>
>>
>> As far as the subject goes, i would say: DO NOT TOUCH binary
>> data.....
>>
>> Simply store it, or not, in your database.....
>
> Bull. Binary data works fine in a database - and storing it there has
> many advantages. Easy backup and improved data integrity are just two
> of them.
>
> A file system is just a database with a different means of access.
> They both store bytes.
Well, there is no harm in *storing* binary data. However, binary data is
no text string and should not be handled as text (first of all, most of
text processing relies on encoding standards, which binary data
obviously do not comply with).
Storing binary data inside a database is perfectly fine. You should not,
however:
* use blobs or large text fields as table indexes, even if text indexing
techniques have been improved
* as a consequence, use them as key for selecting/sorting
* embed them directly in a text request.
Prepared statement have multiple advantages when dealing with
parameters:
* there is no known database-related security issue when dealing with
user supplied parameters as prepared statements bound parameters
(whatever the data encoding or contents)
* the php overhead is reduced when dealing with large or numerous
parameters (no complex/long string is concatenated inside php)
* the database processing is also usually faster because the lexer is
released of most of the request length.
Prepared statements are a best practice, both for performance and
security reasons, go for them ;)
|
|
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182299 is a reply to message #182298] |
Sat, 27 July 2013 20:25 |
Pierre Jaury
Messages: 5
Registered: July 2013
Karma: 0
|
Junior Member |
|
|
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.
|
|
|
|
| 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: 0
|
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
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182301 is a reply to message #182293] |
Sat, 27 July 2013 23:44 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 11:22 AM, Luuk wrote:
> On 27-07-2013 16:36, The Natural Philosopher wrote:
>> On 27/07/13 14:28, Luuk wrote:
>>> On 27-07-2013 13:08, The Natural Philosopher wrote:
>>>> On 27/07/13 10:45, 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.
>>>> >> Also any tips on actually getting the thumbnail data into a
>>>> >> variable -
>>>> >> which package is recommended? I've always used GD, but never been
>>>> >> 100%
>>>> >> happy with it
>>>> >>
>>>> >
>>>> > As far as the subject goes, i would say:
>>>> > DO NOT TOUCH binary data.....
>>>> >
>>>> > Simply store it, or not, in your database.....
>>>> Well exactly. the question is how to get it in there reliably using the
>>>> PHP API.
>>>>
>>>>
>>>
>>>
>>> When reading file contents, i see no need to do a mysqli_real_escape.
>>>
>>> <?php
>>> $link = mysqli_connect("localhost","test","test","test") or
>>> die("Error " . mysqli_error($link));
>>>
>>> $query = "CREATE TABLE `testpng` ( `id` int(11) NOT NULL
>>> AUTO_INCREMENT, `image` blob, PRIMARY KEY (`id`))";
>>> $result = $link->query($query);
>>>
>>> if (!$result) { echo "Could not create table....\n"; exit; } else {
>>> $stmt = $link->prepare("INSERT INTO testpng (image) VALUES (?)");
>>> $file = file_get_contents('upArrow.png');
>>> echo "Size before: ".strlen($file)."<br>\n";
>>> $stmt->bind_param('s', $file);
>>> $stmt->execute();
>>> printf("%d Row inserted.\n", $stmt->affected_rows);
>>> $stmt->close();
>>>
>>> $stmt = $link->query("SELECT image FROM testpng;");
>>> $row = $stmt->fetch_object();
>>> $file = $row->image;
>>> echo "Size before: ".strlen($file)."<br>\n";
>>> }
>>> ?>
>> ..and hope that the file 'upArrow.png' does not contain a ['] or three?
>>
>
> luuk@opensuse:~/public_html/temp> grep "'" upArrow.png
> Binary file upArrow.png matches
>
> luuk@opensuse:~/public_html/temp> hexdump -C upArrow.png | grep "'"
> 00000110 53 00 a0 04 00 60 cb 63 62 e3 00 50 2d 00 60 27
> |S....`.cb..P-.`'|
> 00000260 00 b0 6a 3e 01 7b 91 2d a8 5d 63 03 f6 4b 27 10
> |..j>.{.-.]c..K'.|
> 000003a0 51 c2 27 22 93 a8 4b b4 26 ba 11 f9 c4 18 62 32
> |Q.'"..K.&.....b2|
> 000003c0 37 24 12 89 43 32 27 b9 90 02 49 b1 a4 54 d2 12
> |7$..C2'...I..T..|
> 00000530 fb 81 0e 41 c7 4a 27 5c 27 47 67 8f ce 05 9d e7
> |...A.J'\'Gg.....|
> 00000720 0e 85 50 7e e8 d6 d0 07 61 e6 61 8b c3 7e 0c 27
> |..P~....a.a..~.'|
> 000007c0 91 bc 35 79 24 c5 33 a5 2c e5 b9 84 27 a9 90 bc
> |..5y$.3.,...'...|
> 000009e0 dd bd f3 7a 6f f7 c5 f7 f5 df 16 dd 7e 72 27 fd
> |...zo.......~r'.|
> 000009f0 ce cb bb d9 77 27 ee ad bc 4f bc 5f f4 40 ed 41
> |....w'...O._.@.A|
>
>
> Yes it does, but who cares?
>
> Are you really that afraid for code-insertion, that you fail to know
> that this cannot go wrong?
>
> Or can you explain me how i can kill my database with this?
>
Insert the data without using prepared statements or
mysql_real_escape_string(). Then don't check the return code.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182302 is a reply to message #182299] |
Sat, 27 July 2013 23:46 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 27/07/13 21: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.
Finally an Answer.
> 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.
--
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.
|
|
|
|
| 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: 0
|
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182304 is a reply to message #182297] |
Sat, 27 July 2013 23:52 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 2:10 PM, Pierre Jaury wrote:
> Jerry Stuckle <jstucklex(at)attglobal(dot)net> writes:
>
>> On 7/27/2013 5:45 AM, 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. Also any tips on actually getting the thumbnail data into a
>>>> variable - which package is recommended? I've always used GD, but
>>>> never been 100% happy with it
>>>>
>>>
>>> As far as the subject goes, i would say: DO NOT TOUCH binary
>>> data.....
>>>
>>> Simply store it, or not, in your database.....
>>
>> Bull. Binary data works fine in a database - and storing it there has
>> many advantages. Easy backup and improved data integrity are just two
>> of them.
>>
>> A file system is just a database with a different means of access.
>> They both store bytes.
>
> Well, there is no harm in *storing* binary data. However, binary data is
> no text string and should not be handled as text (first of all, most of
> text processing relies on encoding standards, which binary data
> obviously do not comply with).
>
> Storing binary data inside a database is perfectly fine. You should not,
> however:
> * use blobs or large text fields as table indexes, even if text indexing
> techniques have been improved
> * as a consequence, use them as key for selecting/sorting
> * embed them directly in a text request.
>
Yes and no. There is no means of identifying binary data by itself.
SQL inserts it as a string. You can use prepared statements (extra
overhead because it requires an extra call to the server), or you can
just insert it as a string using mysql_real_escape_string() (which
should be handled by the client library on the local machine instead).
> Prepared statement have multiple advantages when dealing with
> parameters:
> * there is no known database-related security issue when dealing with
> user supplied parameters as prepared statements bound parameters
> (whatever the data encoding or contents)
Properly escaped and handled, there are no database-related security
issues with direct selects, either.
> * the php overhead is reduced when dealing with large or numerous
> parameters (no complex/long string is concatenated inside php)
Actually, PHP overhead is INCREASED due to multiple statements making
calls which have to go all of the way to the server. This overhead is
much less than string concatenation.
> * the database processing is also usually faster because the lexer is
> released of most of the request length.
>
See above about database processing requiring more overhead.
> Prepared statements are a best practice, both for performance and
> security reasons, go for them ;)
>
There is no "best practice" for all occasions, and anyone who claims
there are does not understand all of the issues involved. What is
"best" is very dependent on the individual situation.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182305 is a reply to message #182299] |
Sat, 27 July 2013 23:56 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 4:25 PM, 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).
>
Incorrect. ALL strings, whether user-supplied or not, need to be
escaped if you don't use bound values.
> 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.
>
Additional overhead, as I indicated before. Additionally, binding
numeric values greatly increases the overhead (because they do not need
to be escaped when not using bound variables). They just need to be
validated as the appropriate numeric values (which needs to be done in
either case).
> 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.
>
A little knowledge is a dangerous thing.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182306 is a reply to message #182298] |
Sat, 27 July 2013 23:58 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 2:38 PM, Denis McMahon wrote:
> 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?
>
Yes. If you're not using prepared statements, you need to escape the
binary data (because it is handled as a string in the SQL statement).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182307 is a reply to message #182282] |
Sun, 28 July 2013 00:01 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 07/27/2013 09:56 AM, Jerry Stuckle wrote:
> On 7/27/2013 9:35 AM, Norman Peelman wrote:
>> On 07/27/2013 05:31 AM, The Natural Philosopher wrote:
>>> The target is to create and store thumbnail PNG images in a Mysql table.
>>> Also any tips on actually getting the thumbnail data into a variable -
>>> which package is recommended? I've always used GD, but never been 100%
>>> happy with it
>>>
>>
>> Well, you don't sat why you don't like GD but that is what I use (on
>> one of my sites) so here is what I do---
>>
>> When writing *to* the database:
>> A) You have sql LOAD_FILE to store a file (filesystem) in the db.
>>
>> I write the GD image resource to a tmp file and then store it using
>> LOAD_FILE.
>>
>> imagepng($img, $destfile);
>> /* stuff */
>> $query = "UPDATE table SET img_data = LOAD_FILE('$destfile') WHERE ?"
>>
>
> No, you do NOT have to write the image to a temp file and use LOAD FILE.
> It can be written directly from the binary image in memory.
>
Thanks Jerry, never thought about trying that. My site only used
small images 10kb or less in size and I think I was either processing
them through shared memory (shm - linux) or I may have even just used
the images straight from the POST array (after some verification).
Probably not a good idea but I was just starting to learn PHP/MYSQL/HTML
at the time, not that I've mastered anything...
--
Norman
Registered Linux user #461062
-Have you been to www.php.net yet?-
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182310 is a reply to message #182305] |
Sun, 28 July 2013 01:55 |
Pierre Jaury
Messages: 5
Registered: July 2013
Karma: 0
|
Junior Member |
|
|
Jerry Stuckle <jstucklex(at)attglobal(dot)net> writes:
> On 7/27/2013 4:25 PM, 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).
>>
>
> Incorrect. ALL strings, whether user-supplied or not, need to be
> escaped if you don't use bound values.
Well, mostly depends on what is meant by "user-supplied". I realize I
was not explicit enough, so: escaping strings is useful when crafting
interpreted code based on values that are not built in the local
scope, ie. parameters to your crafting process (which I mentionned and
will keep mentionning as user-supplied data, wether it is obtained from
a human operator, a file or basically any routine that is external to
the crafting scope).
>> 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.
>>
>
> Additional overhead, as I indicated before. Additionally, binding
> numeric values greatly increases the overhead (because they do not
> need to be escaped when not using bound variables). They just need to
> be validated as the appropriate numeric values (which needs to be done
> in either case).
>
There is, indeed, overhead due to the two-step model (preparation, then
execution). However, on a theorical basis, except that operational
overhead, preparing statements mostly have advantages :
* only the symbolic statement is sent as text, data can be shipped in a
proper binary form (which i believe MySQL does as part of a separate
query protocol),
* as a consequence, no need to build and encode strings on the client
side,
* no need to decode those strings on the server side, the server lexer
may process smaller strings,
* no need to escape text data at any time,
* data-independant statement optimizations may be cached.
Even if prepared statements sound theorically ideal, they still *do*
have a couple of conceptual drawbacks:
* replicating queries on multiple servers is much more complex (which
i believe mysql works around by building fully formatted queries sent to
binary logs),
* logging queries suffers the same complexity (requires that data is
encoded and a full text or binary query built on the server side),
* obviously many other related issues.
I am not really into benchmarks and I did not write applications big
enough based on recent MySQL versions to provide some actual feedback, I
suspect however that MySQL imlementation of prepared statements is still
sketchy (especially on the logging, replication and caching sides,
apparently). Maybe performance will improve with future versions of the
statement engine.
I would really like to read some real-world performance feedback on the
matter, if you have some interesting links to share :)
> A little knowledge is a dangerous thing.
I do not feel in a trolling mood, please stop these.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182311 is a reply to message #182310] |
Sun, 28 July 2013 02:11 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/27/2013 9:55 PM, Pierre Jaury wrote:
> Jerry Stuckle <jstucklex(at)attglobal(dot)net> writes:
>
>> On 7/27/2013 4:25 PM, 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).
>>>
>>
>> Incorrect. ALL strings, whether user-supplied or not, need to be
>> escaped if you don't use bound values.
>
> Well, mostly depends on what is meant by "user-supplied". I realize I
> was not explicit enough, so: escaping strings is useful when crafting
> interpreted code based on values that are not built in the local
> scope, ie. parameters to your crafting process (which I mentionned and
> will keep mentionning as user-supplied data, wether it is obtained from
> a human operator, a file or basically any routine that is external to
> the crafting scope).
>
They are also required when using strings built in the local scope. In
fact, ANY strings should be processed by mysql_real_escape_string() if
not using prepared statements. You never know, for instance, if someone
later doesn't add a string with a single quote in it.
>>> 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.
>>>
>>
>> Additional overhead, as I indicated before. Additionally, binding
>> numeric values greatly increases the overhead (because they do not
>> need to be escaped when not using bound variables). They just need to
>> be validated as the appropriate numeric values (which needs to be done
>> in either case).
>>
>
> There is, indeed, overhead due to the two-step model (preparation, then
> execution). However, on a theorical basis, except that operational
> overhead, preparing statements mostly have advantages :
>
Theoretical basis is nice. But PRACTICAL basis is usable.
> * only the symbolic statement is sent as text, data can be shipped in a
> proper binary form (which i believe MySQL does as part of a separate
> query protocol),
Which means additional calls to the server.
> * as a consequence, no need to build and encode strings on the client
> side,
No. It just means the strings need to be shipped separately via
individual calls to the server and assembled there. If the server is
busy, this can also add to performance problem.
> * no need to decode those strings on the server side, the server lexer
> may process smaller strings,
No need to decode the strings otherwise, either. And the server lexer
will still have to assemble the entire string before processing, or
process multiple smaller strings. Another performance hit.
> * no need to escape text data at any time,
No, but escaping strings is not that hard. And numeric values do not
require escaping - but will still result in additional calls required to
bind those parameters in a prepared statement.
> * data-independant statement optimizations may be cached.
>
Using non-prepared statements doesn't change this.
> Even if prepared statements sound theorically ideal, they still *do*
> have a couple of conceptual drawbacks:
>
Nothing is "ideal".
> * replicating queries on multiple servers is much more complex (which
> i believe mysql works around by building fully formatted queries sent to
> binary logs),
Replication is not dependent on whether you use prepared statements or not.
> * logging queries suffers the same complexity (requires that data is
> encoded and a full text or binary query built on the server side),
> * obviously many other related issues.
>
It must be encoded and built to process the statement anyway.
> I am not really into benchmarks and I did not write applications big
> enough based on recent MySQL versions to provide some actual feedback, I
> suspect however that MySQL imlementation of prepared statements is still
> sketchy (especially on the logging, replication and caching sides,
> apparently). Maybe performance will improve with future versions of the
> statement engine.
>
Performance can be critical in some cases. It is something a good
programmer ALWAYS takes into consideration when writing code.
> I would really like to read some real-world performance feedback on the
> matter, if you have some interesting links to share :)
>
You can google as well as I can. Or you can run your own benchmarks,
like I have.
>> A little knowledge is a dangerous thing.
>
> I do not feel in a trolling mood, please stop these.
>
No trolling. Just an observation you are obviously aware of only SOME
of the processing involved, and have made incorrect statements based on
that limited knowledge.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182320 is a reply to message #182301] |
Sun, 28 July 2013 12:54 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28-07-2013 01:44, Jerry Stuckle wrote:
> On 7/27/2013 11:22 AM, Luuk wrote:
>> On 27-07-2013 16:36, The Natural Philosopher wrote:
>>> On 27/07/13 14:28, Luuk wrote:
>>>> On 27-07-2013 13:08, The Natural Philosopher wrote:
>>>> > On 27/07/13 10:45, 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.
>>>> >>> Also any tips on actually getting the thumbnail data into a
>>>> >>> variable -
>>>> >>> which package is recommended? I've always used GD, but never been
>>>> >>> 100%
>>>> >>> happy with it
>>>> >>>
>>>> >>
>>>> >> As far as the subject goes, i would say:
>>>> >> DO NOT TOUCH binary data.....
>>>> >>
>>>> >> Simply store it, or not, in your database.....
>>>> > Well exactly. the question is how to get it in there reliably using
>>>> > the
>>>> > PHP API.
>>>> >
>>>> >
>>>>
>>>>
>>>> When reading file contents, i see no need to do a mysqli_real_escape.
>>>>
>>>> <?php
>>>> $link = mysqli_connect("localhost","test","test","test") or
>>>> die("Error " . mysqli_error($link));
>>>>
>>>> $query = "CREATE TABLE `testpng` ( `id` int(11) NOT NULL
>>>> AUTO_INCREMENT, `image` blob, PRIMARY KEY (`id`))";
>>>> $result = $link->query($query);
>>>>
>>>> if (!$result) { echo "Could not create table....\n"; exit; } else {
>>>> $stmt = $link->prepare("INSERT INTO testpng (image) VALUES (?)");
>>>> $file = file_get_contents('upArrow.png');
>>>> echo "Size before: ".strlen($file)."<br>\n";
>>>> $stmt->bind_param('s', $file);
>>>> $stmt->execute();
>>>> printf("%d Row inserted.\n", $stmt->affected_rows);
>>>> $stmt->close();
>>>>
>>>> $stmt = $link->query("SELECT image FROM testpng;");
>>>> $row = $stmt->fetch_object();
>>>> $file = $row->image;
>>>> echo "Size before: ".strlen($file)."<br>\n";
>>>> }
>>>> ?>
>>> ..and hope that the file 'upArrow.png' does not contain a ['] or three?
>>>
>>
>> luuk@opensuse:~/public_html/temp> grep "'" upArrow.png
>> Binary file upArrow.png matches
>>
>> luuk@opensuse:~/public_html/temp> hexdump -C upArrow.png | grep "'"
>> 00000110 53 00 a0 04 00 60 cb 63 62 e3 00 50 2d 00 60 27
>> |S....`.cb..P-.`'|
>> 00000260 00 b0 6a 3e 01 7b 91 2d a8 5d 63 03 f6 4b 27 10
>> |..j>.{.-.]c..K'.|
>> 000003a0 51 c2 27 22 93 a8 4b b4 26 ba 11 f9 c4 18 62 32
>> |Q.'"..K.&.....b2|
>> 000003c0 37 24 12 89 43 32 27 b9 90 02 49 b1 a4 54 d2 12
>> |7$..C2'...I..T..|
>> 00000530 fb 81 0e 41 c7 4a 27 5c 27 47 67 8f ce 05 9d e7
>> |...A.J'\'Gg.....|
>> 00000720 0e 85 50 7e e8 d6 d0 07 61 e6 61 8b c3 7e 0c 27
>> |..P~....a.a..~.'|
>> 000007c0 91 bc 35 79 24 c5 33 a5 2c e5 b9 84 27 a9 90 bc
>> |..5y$.3.,...'...|
>> 000009e0 dd bd f3 7a 6f f7 c5 f7 f5 df 16 dd 7e 72 27 fd
>> |...zo.......~r'.|
>> 000009f0 ce cb bb d9 77 27 ee ad bc 4f bc 5f f4 40 ed 41
>> |....w'...O._.@.A|
>>
>>
>> Yes it does, but who cares?
>>
>> Are you really that afraid for code-insertion, that you fail to know
>> that this cannot go wrong?
>>
>> Or can you explain me how i can kill my database with this?
>>
>
> Insert the data without using prepared statements or
> mysql_real_escape_string().
>
>
I think i was using a prepared statement.
And i wonder why i need to check if the contents of this png-file is
really a png-file, or maybe a script. I'm not executing any code that
MIGHT be stored in this file. I'm just storing the contents of that file
in some blob.
> Then don't check the return code.
I did not check return code because this was a piece of example,
normally is would check return code to see if storing was successful...
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182321 is a reply to message #182320] |
Sun, 28 July 2013 13:35 |
J.O. Aho
Messages: 194
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28/07/13 14:54, Luuk wrote:
> I think i was using a prepared statement.
> And i wonder why i need to check if the contents of this png-file is
> really a png-file, or maybe a script. I'm not executing any code that
> MIGHT be stored in this file. I'm just storing the contents of that file
> in some blob.
If you worry that the image data includes something that may cause the
database do something else than just insert the image, then see to
base64 encode it first, this way you will not be affected by the
content, of course you make things a bit more "heavy" for the system as
you will need to decode the blob each time you want to use the image,
but then if you use some sort of cache then you can store the decoded
blob in the cache and use it as long as it residence there.
--
//Aho
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182323 is a reply to message #182320] |
Sun, 28 July 2013 13:39 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/28/2013 8:54 AM, Luuk wrote:
> On 28-07-2013 01:44, Jerry Stuckle wrote:
>> On 7/27/2013 11:22 AM, Luuk wrote:
>>> On 27-07-2013 16:36, The Natural Philosopher wrote:
>>>> On 27/07/13 14:28, Luuk wrote:
>>>> > On 27-07-2013 13:08, The Natural Philosopher wrote:
>>>> >> On 27/07/13 10:45, 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.
>>>> >>>> Also any tips on actually getting the thumbnail data into a
>>>> >>>> variable -
>>>> >>>> which package is recommended? I've always used GD, but never been
>>>> >>>> 100%
>>>> >>>> happy with it
>>>> >>>>
>>>> >>>
>>>> >>> As far as the subject goes, i would say:
>>>> >>> DO NOT TOUCH binary data.....
>>>> >>>
>>>> >>> Simply store it, or not, in your database.....
>>>> >> Well exactly. the question is how to get it in there reliably using
>>>> >> the
>>>> >> PHP API.
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>> > When reading file contents, i see no need to do a mysqli_real_escape.
>>>> >
>>>> > <?php
>>>> > $link = mysqli_connect("localhost","test","test","test") or
>>>> > die("Error " . mysqli_error($link));
>>>> >
>>>> > $query = "CREATE TABLE `testpng` ( `id` int(11) NOT NULL
>>>> > AUTO_INCREMENT, `image` blob, PRIMARY KEY (`id`))";
>>>> > $result = $link->query($query);
>>>> >
>>>> > if (!$result) { echo "Could not create table....\n"; exit; } else {
>>>> > $stmt = $link->prepare("INSERT INTO testpng (image) VALUES (?)");
>>>> > $file = file_get_contents('upArrow.png');
>>>> > echo "Size before: ".strlen($file)."<br>\n";
>>>> > $stmt->bind_param('s', $file);
>>>> > $stmt->execute();
>>>> > printf("%d Row inserted.\n", $stmt->affected_rows);
>>>> > $stmt->close();
>>>> >
>>>> > $stmt = $link->query("SELECT image FROM testpng;");
>>>> > $row = $stmt->fetch_object();
>>>> > $file = $row->image;
>>>> > echo "Size before: ".strlen($file)."<br>\n";
>>>> > }
>>>> > ?>
>>>> ..and hope that the file 'upArrow.png' does not contain a ['] or three?
>>>>
>>>
>>> luuk@opensuse:~/public_html/temp> grep "'" upArrow.png
>>> Binary file upArrow.png matches
>>>
>>> luuk@opensuse:~/public_html/temp> hexdump -C upArrow.png | grep "'"
>>> 00000110 53 00 a0 04 00 60 cb 63 62 e3 00 50 2d 00 60 27
>>> |S....`.cb..P-.`'|
>>> 00000260 00 b0 6a 3e 01 7b 91 2d a8 5d 63 03 f6 4b 27 10
>>> |..j>.{.-.]c..K'.|
>>> 000003a0 51 c2 27 22 93 a8 4b b4 26 ba 11 f9 c4 18 62 32
>>> |Q.'"..K.&.....b2|
>>> 000003c0 37 24 12 89 43 32 27 b9 90 02 49 b1 a4 54 d2 12
>>> |7$..C2'...I..T..|
>>> 00000530 fb 81 0e 41 c7 4a 27 5c 27 47 67 8f ce 05 9d e7
>>> |...A.J'\'Gg.....|
>>> 00000720 0e 85 50 7e e8 d6 d0 07 61 e6 61 8b c3 7e 0c 27
>>> |..P~....a.a..~.'|
>>> 000007c0 91 bc 35 79 24 c5 33 a5 2c e5 b9 84 27 a9 90 bc
>>> |..5y$.3.,...'...|
>>> 000009e0 dd bd f3 7a 6f f7 c5 f7 f5 df 16 dd 7e 72 27 fd
>>> |...zo.......~r'.|
>>> 000009f0 ce cb bb d9 77 27 ee ad bc 4f bc 5f f4 40 ed 41
>>> |....w'...O._.@.A|
>>>
>>>
>>> Yes it does, but who cares?
>>>
>>> Are you really that afraid for code-insertion, that you fail to know
>>> that this cannot go wrong?
>>>
>>> Or can you explain me how i can kill my database with this?
>>>
>>
>> Insert the data without using prepared statements or
>> mysql_real_escape_string().
>>
>>
>
> I think i was using a prepared statement.
> And i wonder why i need to check if the contents of this png-file is
> really a png-file, or maybe a script. I'm not executing any code that
> MIGHT be stored in this file. I'm just storing the contents of that file
> in some blob.
>
>> Then don't check the return code.
>
> I did not check return code because this was a piece of example,
> normally is would check return code to see if storing was successful...
>
>
It doesn't make any difference. The png could contain one or more
delimiters, i.e. an apostrophe, which will cause a syntax error in a
non-prepared statement.
Escaping strings is not just to prevent security leeks - it is also to
ensure valid SQL.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182326 is a reply to message #182323] |
Sun, 28 July 2013 15:25 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28-07-2013 15:39, Jerry Stuckle wrote:
> On 7/28/2013 8:54 AM, Luuk wrote:
>>>> > ..and hope that the file 'upArrow.png' does not contain a ['] or
>>>> > three?
>>>> >
>>>>
>>>> luuk@opensuse:~/public_html/temp> grep "'" upArrow.png
>>>> Binary file upArrow.png matches
>>>>
>>>> luuk@opensuse:~/public_html/temp> hexdump -C upArrow.png | grep "'"
>>>> 00000110 53 00 a0 04 00 60 cb 63 62 e3 00 50 2d 00 60 27
>>>> |S....`.cb..P-.`'|
>>>> 00000260 00 b0 6a 3e 01 7b 91 2d a8 5d 63 03 f6 4b 27 10
>>>> |..j>.{.-.]c..K'.|
>>>> 000003a0 51 c2 27 22 93 a8 4b b4 26 ba 11 f9 c4 18 62 32
>>>> |Q.'"..K.&.....b2|
>>>> 000003c0 37 24 12 89 43 32 27 b9 90 02 49 b1 a4 54 d2 12
>>>> |7$..C2'...I..T..|
>>>> 00000530 fb 81 0e 41 c7 4a 27 5c 27 47 67 8f ce 05 9d e7
>>>> |...A.J'\'Gg.....|
>>>> 00000720 0e 85 50 7e e8 d6 d0 07 61 e6 61 8b c3 7e 0c 27
>>>> |..P~....a.a..~.'|
>>>> 000007c0 91 bc 35 79 24 c5 33 a5 2c e5 b9 84 27 a9 90 bc
>>>> |..5y$.3.,...'...|
>>>> 000009e0 dd bd f3 7a 6f f7 c5 f7 f5 df 16 dd 7e 72 27 fd
>>>> |...zo.......~r'.|
>>>> 000009f0 ce cb bb d9 77 27 ee ad bc 4f bc 5f f4 40 ed 41
>>>> |....w'...O._.@.A|
>>>>
>>>>
>
> It doesn't make any difference. The png could contain one or more
> delimiters, i.e. an apostrophe, which will cause a syntax error in a
> non-prepared statement.
It DOES contain single-quotes (that is an apostophe?).
and that DOES NOT cause a syntax error,
because i'm using a prepared statement.
>
> Escaping strings is not just to prevent security leeks - it is also to
> ensure valid SQL.
>
I dont see any errors in the query.log or error.log of my MySQL
installation (about updating this `testpng`-table).
Therefore i dont see a point in ensuring validity of an already valid SQL.
Can you give an example an file that would break my script?
The script is here: http://pastebin.com/5D8ZGEhy
My emailadress is: luuk34(at)gmail(dot)com
I think i have shown that this script is working OK, and that there is
no reason to do something with escaping string here.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182327 is a reply to message #182303] |
Sun, 28 July 2013 15:44 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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 ;)
> I'd really like to know why..
>
Me likes to know why to
|
|
|
|
| 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: 0
|
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182331 is a reply to message #182327] |
Sun, 28 July 2013 16:39 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28/07/13 16:44, Luuk wrote:
>
> 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 ;)
>
>> I'd really like to know why..
>>
>
> Me likes to know why to
>
Ok. Lets take a step back and summarise - and feel free to correct me if
I am wrong.
1/. Mysql can store anything in a BLOB.
2/. Using prepared statements binary data in a 'string' variable will
be stored correctly via the PHP API.
3/. What about un-prepared statements like:
(getting data out is not a major issue)
$blob=file_get_contents('mygraffix.png')
mysqli_query($link, sprintf("insert into mytable set myblob='%s'",$blob));
Presumably that will barf at some point because the PHP itself will get
confused about where the string begins and ends?
Or does it? I suppose its down to the way PHP parses the query string
and sends it.
Which is why the 'prepared' statement or 'Load_file()' options are
preferred?
i.e. the problem is not with mysql per se, but with PHPs way of handling
strings..
In C of course you simply use mysql_real_query() and specify the query
length..
But I can't actually see how even that will work.. OK you now how long
the total statement has to be, but
at some level you are going to have a statement like 'update mytable,
set bmyblob=randombinarydatapossibly_containing,set
something_else=somethingelse'
That is, simply knowing the completed query LENGTH does not remove
ambiguity.
Where this leaves me is essentially that methods (1) and (2) above are
the only reliable ways to do this job.
I'd like that confirmed or denied..
In the past I have always used load_file with no real issues, but in the
new application security is of major concern. I don't want the average
uploader of images to have general FILE access.
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182332 is a reply to message #182329] |
Sun, 28 July 2013 16:43 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28/07/13 17:01, Pierre Jaury wrote:
> Luuk <luuk(at)invalid(dot)lan> writes:
>
>
> 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).
I'll read that. Thank you
> 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.
Ah. At last the detail I was after. THANK YOU.
Do I take it that data intended fora BLOB column is NOT pre processed,
and data flagged as binary is not pre processed, either.
> 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.
time will tell.
In the end this whole question is about how to get binary data into a
blob object using php in the most efficient and reliable way possible.
Its a fairly common problem that I found the documentation did not
adequately handle.
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182333 is a reply to message #182331] |
Sun, 28 July 2013 17:56 |
Pierre Jaury
Messages: 5
Registered: July 2013
Karma: 0
|
Junior Member |
|
|
The Natural Philosopher <tnp(at)invalid(dot)invalid> writes:
> On 28/07/13 16:44, Luuk wrote:
>>
>> 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 ;)
>>
>>> I'd really like to know why..
>>>
>>
>> Me likes to know why to
>>
> Ok. Lets take a step back and summarise - and feel free to correct me
> if I am wrong.
>
> 1/. Mysql can store anything in a BLOB.
> 2/. Using prepared statements binary data in a 'string' variable will
> be stored correctly via the PHP API.
> 3/. What about un-prepared statements like:
>
> (getting data out is not a major issue)
>
> $blob=file_get_contents('mygraffix.png')
>
> mysqli_query($link, sprintf("insert into mytable set
> myblob='%s'",$blob));
This were string escaping is required
(eg. mysqli_real_escape_string($blob)). Rule of thumb: if you are using
uncontrolled data, and more generally any string that is not explicitely
declared in the current scope and that might, for one reason or another,
interfere with the MySQL query language, you MUST escape it before
constructing your text query.
> Presumably that will barf at some point because the PHP itself will
> get confused about where the string begins and ends?
>
> Or does it? I suppose its down to the way PHP parses the query string
> and sends it.
PHP does not parse anything, in that very case, PHP simply builds the
query string by running sprintf then sends the result to the MySQL C
API, as a string. I am not sure how PHP will handle or how the API will
react if you pass a query containing null bytes for instance, but that
is out of the question because you did things properly and did escape
every piece of data that required escaping.
I think you are confusing many notions here, even more than I do
according to Jerry :) As long as you escape your strings when building
text queries, PHP will pass them to the MySQL API, which will send them
to the server, which won't bother parsing and executing your query.
You may, on the other hand, use bound parameters, which simply do not
require you to escape the parameter string because it is processed
completely separately from the sql syntax.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182334 is a reply to message #182333] |
Sun, 28 July 2013 18:14 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28/07/13 18:56, Pierre Jaury wrote:
> The Natural Philosopher <tnp(at)invalid(dot)invalid> writes:
>
>> On 28/07/13 16:44, Luuk wrote:
>>> 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 ;)
>>>
>>>> I'd really like to know why..
>>>>
>>> Me likes to know why to
>>>
>> Ok. Lets take a step back and summarise - and feel free to correct me
>> if I am wrong.
>>
>> 1/. Mysql can store anything in a BLOB.
>> 2/. Using prepared statements binary data in a 'string' variable will
>> be stored correctly via the PHP API.
>> 3/. What about un-prepared statements like:
>>
>> (getting data out is not a major issue)
>>
>> $blob=file_get_contents('mygraffix.png')
>>
>> mysqli_query($link, sprintf("insert into mytable set
>> myblob='%s'",$blob));
> This were string escaping is required
> (eg. mysqli_real_escape_string($blob)). Rule of thumb: if you are using
> uncontrolled data, and more generally any string that is not explicitely
> declared in the current scope and that might, for one reason or another,
> interfere with the MySQL query language, you MUST escape it before
> constructing your text query.
>
>> Presumably that will barf at some point because the PHP itself will
>> get confused about where the string begins and ends?
>>
>> Or does it? I suppose its down to the way PHP parses the query string
>> and sends it.
> PHP does not parse anything, in that very case, PHP simply builds the
> query string by running sprintf then sends the result to the MySQL C
> API, as a string. I am not sure how PHP will handle or how the API will
> react if you pass a query containing null bytes for instance, but that
> is out of the question because you did things properly and did escape
> every piece of data that required escaping.
>
> I think you are confusing many notions here, even more than I do
> according to Jerry :) As long as you escape your strings when building
> text queries, PHP will pass them to the MySQL API, which will send them
> to the server, which won't bother parsing and executing your query.
I think in the end it boils down to the basic issue that if you pass a
non text string AS a text string, you have to escape it, and the effects
of doing that into a binary target are not necessarily guranteed
And there are only two ways to pass pure binary data AS pure binary
data, - using the prepare-statement way or load_file()
(I never pay attention to Jerry. He is far more concerned with his own
ego than actually providing anything of any value.)
> You may, on the other hand, use bound parameters, which simply do not
> require you to escape the parameter string because it is processed
> completely separately from the sql syntax.
Exactly so.
And that is in fact the way I will go in this instance, since it matches
the nature of the problems most closely.
--
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.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182336 is a reply to message #182329] |
Sun, 28 July 2013 18:31 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28-07-2013 18:01, Pierre Jaury wrote:
> 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.
>
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 ;)
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]