| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182338 is a reply to message #182326] |
Sun, 28 July 2013 19:05   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/28/2013 11:25 AM, Luuk wrote:
> 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.
>
You're not reading my replies, Luuk. I said if it contains delimiters
and YOU ARE NOT USING PREPARED STATEMENTS. For instance:
"INSERT INTO MyTABLE (bin_column) VALUES ('$png_data');
Prepared statements are good for some things - but you have to also be
aware of the additional processing required when you use them.
--
==================
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 #182339 is a reply to message #182334] |
Sun, 28 July 2013 19:10 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/28/2013 2:14 PM, The Natural Philosopher wrote:
>
> (I never pay attention to Jerry. He is far more concerned with his own
> ego than actually providing anything of any value.)
>
Nobody ever pays attention to you, either, just like with any anonymous
troll. You won't use a real name or email because you're so afraid
people will find out you're neither a programmer nor the EE you claim to be.
Don't worry - we all know that anyway.
As for ego - you're the one who's ego is so weak you're afraid to use
your real name. But you have to blast anyone who is more knowledgeable
than you (not hard to find in these newsgroups).
--
==================
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 #182340 is a reply to message #182338] |
Sun, 28 July 2013 19:16 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 28-07-2013 21:05, Jerry Stuckle wrote:
> On 7/28/2013 11:25 AM, Luuk wrote:
>> 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.
>>
>
> You're not reading my replies, Luuk. I said if it contains delimiters
> and YOU ARE NOT USING PREPARED STATEMENTS. For instance:
>
> "INSERT INTO MyTABLE (bin_column) VALUES ('$png_data');
>
> Prepared statements are good for some things - but you have to also be
> aware of the additional processing required when you use them.
>
>
From my code (at: http://pastebin.com/5D8ZGEhy):
$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);
Is above not a prepared statment?
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182341 is a reply to message #182340] |
Mon, 29 July 2013 00:46 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/28/2013 3:16 PM, Luuk wrote:
> On 28-07-2013 21:05, Jerry Stuckle wrote:
>> On 7/28/2013 11:25 AM, Luuk wrote:
>>> 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.
>>>
>>
>> You're not reading my replies, Luuk. I said if it contains delimiters
>> and YOU ARE NOT USING PREPARED STATEMENTS. For instance:
>>
>> "INSERT INTO MyTABLE (bin_column) VALUES ('$png_data');
>>
>> Prepared statements are good for some things - but you have to also be
>> aware of the additional processing required when you use them.
>>
>>
>
> From my code (at: http://pastebin.com/5D8ZGEhy):
> $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);
>
> Is above not a prepared statment?
>
Luuk, yes it is. But READ WHAT I'VE BEEN SAYING!
I NEVER SAID YOU WERE NOT USING PREPARED STATEMENTS!
--
==================
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 #182342 is a reply to message #182331] |
Mon, 29 July 2013 01:13 |
Norman Peelman
Messages: 126
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 07/28/2013 12:39 PM, The Natural Philosopher wrote:
> 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.
>
>
http://dev.mysql.com/doc/refman/5.0/en/blob.html
--
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 #182344 is a reply to message #182341] |
Mon, 29 July 2013 16:17 |
Daniel Pitts
Messages: 68
Registered: May 2012
Karma: 0
|
Member |
|
|
On 7/28/13 5:46 PM, Jerry Stuckle wrote:
> On 7/28/2013 3:16 PM, Luuk wrote:
>> On 28-07-2013 21:05, Jerry Stuckle wrote:
>>> On 7/28/2013 11:25 AM, Luuk wrote:
>>>> 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.
>>>>
>>>
>>> You're not reading my replies, Luuk. I said if it contains delimiters
>>> and YOU ARE NOT USING PREPARED STATEMENTS. For instance:
>>>
>>> "INSERT INTO MyTABLE (bin_column) VALUES ('$png_data');
>>>
>>> Prepared statements are good for some things - but you have to also be
>>> aware of the additional processing required when you use them.
If you're concerned about additional processing, then you shouldn't be
using PHP in the first place :-) Chances are there are aspects of the
program which use more resources than prepared statements.
I would posit that one should use prepared statements for everything,
*unless* there is a critical performance requirement which cannot be met
with them *and* you can provide for safe data.
Profiling is necessary for determining performance penalty, and rigorous
analysis is required for the safety. So, only highly skilled
professionals with the appropriate tools should consider eschewing
prepared statements. It seems to be an unfortunate reversal, where most
highly skilled professionals always use prepared statements, and
amateurs and students unwittingly use unsafe alternatives.
>>>
>>>
>>
>> From my code (at: http://pastebin.com/5D8ZGEhy):
>> $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);
>>
>> Is above not a prepared statment?
Luuk, I understand why you think Jerry was criticizing your code, but I
don't believe he was. Your code is fine WRT SQL injection.
>
> Luuk, yes it is. But READ WHAT I'VE BEEN SAYING!
>
> I NEVER SAID YOU WERE NOT USING PREPARED STATEMENTS!
Jerry, I understand why you are making a point about SQL injection, but
in the context, it comes across as a criticism of Luuk's code, which
actually is correct, and escaping would actually cause problems.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182345 is a reply to message #182344] |
Mon, 29 July 2013 18:01 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/29/2013 12:17 PM, Daniel Pitts wrote:
> On 7/28/13 5:46 PM, Jerry Stuckle wrote:
>> On 7/28/2013 3:16 PM, Luuk wrote:
>>> On 28-07-2013 21:05, Jerry Stuckle wrote:
>>>> On 7/28/2013 11:25 AM, Luuk wrote:
>>>> > 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.
>>>> >
>>>>
>>>> You're not reading my replies, Luuk. I said if it contains delimiters
>>>> and YOU ARE NOT USING PREPARED STATEMENTS. For instance:
>>>>
>>>> "INSERT INTO MyTABLE (bin_column) VALUES ('$png_data');
>>>>
>>>> Prepared statements are good for some things - but you have to also be
>>>> aware of the additional processing required when you use them.
> If you're concerned about additional processing, then you shouldn't be
> using PHP in the first place :-) Chances are there are aspects of the
> program which use more resources than prepared statements.
>
> I would posit that one should use prepared statements for everything,
> *unless* there is a critical performance requirement which cannot be met
> with them *and* you can provide for safe data.
>
> Profiling is necessary for determining performance penalty, and rigorous
> analysis is required for the safety. So, only highly skilled
> professionals with the appropriate tools should consider eschewing
> prepared statements. It seems to be an unfortunate reversal, where most
> highly skilled professionals always use prepared statements, and
> amateurs and students unwittingly use unsafe alternatives.
>
>>>>
>>>>
>>>
>>> From my code (at: http://pastebin.com/5D8ZGEhy):
>>> $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);
>>>
>>> Is above not a prepared statment?
> Luuk, I understand why you think Jerry was criticizing your code, but I
> don't believe he was. Your code is fine WRT SQL injection.
>
>>
>> Luuk, yes it is. But READ WHAT I'VE BEEN SAYING!
>>
>> I NEVER SAID YOU WERE NOT USING PREPARED STATEMENTS!
> Jerry, I understand why you are making a point about SQL injection, but
> in the context, it comes across as a criticism of Luuk's code, which
> actually is correct, and escaping would actually cause problems.
>
Sorry, I do not mean to criticize Luuk's code - in fact, I have
specifically tried NOT to criticize his code. I was only referring to
code which does NOT use prepared statements. It has nothing to do with
how he's writing his 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 #182346 is a reply to message #182341] |
Mon, 29 July 2013 18:34 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 29-07-2013 02:46, Jerry Stuckle wrote:
> On 7/28/2013 3:16 PM, Luuk wrote:
>> On 28-07-2013 21:05, Jerry Stuckle wrote:
.....
>>> and YOU ARE NOT USING PREPARED STATEMENTS. For instance:
>>>
.....
>
> I NEVER SAID YOU WERE NOT USING PREPARED STATEMENTS!
>
>
I must have been my understanding of English..... ;)
But i now know what you mean..., thanks
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182347 is a reply to message #182274] |
Mon, 29 July 2013 18:37 |
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
>
BTW, why is this message cross-posted to 'comp.datbase.mysql'
it's a non-existing newsgroup......
;)
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182349 is a reply to message #182344] |
Mon, 29 July 2013 23:04 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
Further to this thread I THINK I have established a third way to get
clean binary data into a blob.
I looked at what phpmyadmin was doing and developed this code.
( it's just the relevant fragment. Its a screen to upload a single file
and some other form stuff and update the SQL record. )
====================
if($_FILES['uploaded_file']['error']!="")
{
$havefile=FALSE;
}
else
{
$code=file_get_contents($_FILES['uploaded_file']['tmp_name']);
$size=$_FILES['uploaded_file']['size'];
$filename=$_FILES['uploaded_file']['name'];
$havefile=TRUE;
}
if (($id=get_id())>0) // its an update
{
if($havefile)
$query=sprintf("update adminmodule set uri='%s',
descr='%s', privilege_level='%d', filename='%s', size='%d', code=0x%s,
modified_by='%d', modified_on=now() where id='%d'",
$_POST['uri'],$_POST['descr'],$_POST['privilege_level'],$filename,
$size, bin2hex($code), $login_id,$id);
else
$query=sprintf("update adminmodule set uri='%s',
descr='%s', privilege_level='%d', modified_by='%d', modified_on=now()
where id='%d'",
$_POST['uri'],$_POST['descr'],$_POST['privilege_level'],$login_id,$id);
mysqli_query($link,$query);
}
=======================
That is, if you have a variable with binary data in it, run bin2hex()
on it and prepend '0x' to it and throw it at a simple sql update or
insert statement.
I didnt know MySQL accepted hex data in that form.
Not as efficient as a prepared statement for big objects, but its
simple to understand. And it avoids load_file.
In this case its optimal. The code is simple, no FILE privileges are
required. Since inserts and updates are rare things done by a few people
the inefficiency won't load up the server hugely.
--
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 #182350 is a reply to message #182349] |
Tue, 30 July 2013 01:02 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/29/2013 7:04 PM, The Natural Philosopher wrote:
> Further to this thread I THINK I have established a third way to get
> clean binary data into a blob.
> I looked at what phpmyadmin was doing and developed this code.
> ( it's just the relevant fragment. Its a screen to upload a single file
> and some other form stuff and update the SQL record. )
> ====================
> if($_FILES['uploaded_file']['error']!="")
> {
> $havefile=FALSE;
> }
> else
> {
> $code=file_get_contents($_FILES['uploaded_file']['tmp_name']);
> $size=$_FILES['uploaded_file']['size'];
> $filename=$_FILES['uploaded_file']['name'];
> $havefile=TRUE;
> }
> if (($id=get_id())>0) // its an update
> {
> if($havefile)
> $query=sprintf("update adminmodule set uri='%s',
> descr='%s', privilege_level='%d', filename='%s', size='%d', code=0x%s,
> modified_by='%d', modified_on=now() where id='%d'",
> $_POST['uri'],$_POST['descr'],$_POST['privilege_level'],$filename,
> $size, bin2hex($code), $login_id,$id);
> else
> $query=sprintf("update adminmodule set uri='%s',
> descr='%s', privilege_level='%d', modified_by='%d', modified_on=now()
> where id='%d'",
> $_POST['uri'],$_POST['descr'],$_POST['privilege_level'],$login_id,$id);
> mysqli_query($link,$query);
> }
> =======================
> That is, if you have a variable with binary data in it, run bin2hex()
> on it and prepend '0x' to it and throw it at a simple sql update or
> insert statement.
>
> I didnt know MySQL accepted hex data in that form.
>
> Not as efficient as a prepared statement for big objects, but its
> simple to understand. And it avoids load_file.
>
> In this case its optimal. The code is simple, no FILE privileges are
> required. Since inserts and updates are rare things done by a few people
> the inefficiency won't load up the server hugely.
>
A beautiful way to ensure your database gets hacked (not cleaning the
$_POST data before trying to insert into the database).
--
==================
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 #182351 is a reply to message #182347] |
Tue, 30 July 2013 05:31 |
J.O. Aho
Messages: 194
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 29/07/13 20:37, Luuk wrote:
> BTW, why is this message cross-posted to 'comp.datbase.mysql'
I think Denis thought the question was relevant for c.d.m (maybe it
should have been posted in the inactive group alt.php.sql).
> it's a non-existing newsgroup......
Just for your service provider don't supply that news group, id do exist.
--
//Aho
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182352 is a reply to message #182351] |
Tue, 30 July 2013 06:08 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/30/2013 1:31 AM, J.O. Aho wrote:
> On 29/07/13 20:37, Luuk wrote:
>
>> BTW, why is this message cross-posted to 'comp.datbase.mysql'
>
> I think Denis thought the question was relevant for c.d.m (maybe it
> should have been posted in the inactive group alt.php.sql).
>
>
>> it's a non-existing newsgroup......
>
> Just for your service provider don't supply that news group, id do exist.
>
>
My newsgroup provider doesn't carry comp.database.mysql, either. It
does, however, carry comp.databaseS.mysql.
--
==================
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 #182354 is a reply to message #182352] |
Tue, 30 July 2013 12:35 |
Scott Johnson
Messages: 196
Registered: January 2012
Karma: 0
|
Senior Member |
|
|
On 7/29/2013 11:08 PM, Jerry Stuckle wrote:
> On 7/30/2013 1:31 AM, J.O. Aho wrote:
>> On 29/07/13 20:37, Luuk wrote:
>>
>>> BTW, why is this message cross-posted to 'comp.datbase.mysql'
>>
>> I think Denis thought the question was relevant for c.d.m (maybe it
>> should have been posted in the inactive group alt.php.sql).
>>
>>
>>> it's a non-existing newsgroup......
>>
>> Just for your service provider don't supply that news group, id do exist.
>>
>>
>
> My newsgroup provider doesn't carry comp.database.mysql, either. It
> does, however, carry comp.databaseS.mysql.
>
Ditto
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182355 is a reply to message #182352] |
Tue, 30 July 2013 15:53 |
J.O. Aho
Messages: 194
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 30/07/13 08:08, Jerry Stuckle wrote:
> On 7/30/2013 1:31 AM, J.O. Aho wrote:
>> On 29/07/13 20:37, Luuk wrote:
>>
>>> BTW, why is this message cross-posted to 'comp.datbase.mysql'
>>
>> I think Denis thought the question was relevant for c.d.m (maybe it
>> should have been posted in the inactive group alt.php.sql).
>>
>>
>>> it's a non-existing newsgroup......
>>
>> Just for your service provider don't supply that news group, id do exist.
>>
>>
>
> My newsgroup provider doesn't carry comp.database.mysql, either. It
> does, however, carry comp.databaseS.mysql.
I could try to blame it was still early in the morning for me... just
didn't check the spelling that closely.
|
|
|
|
| Re: is mysqli_real_escape_string bullet proof with binary data? [message #182356 is a reply to message #182355] |
Tue, 30 July 2013 16:37 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 7/30/2013 11:53 AM, J.O. Aho wrote:
> On 30/07/13 08:08, Jerry Stuckle wrote:
>> On 7/30/2013 1:31 AM, J.O. Aho wrote:
>>> On 29/07/13 20:37, Luuk wrote:
>>>
>>>> BTW, why is this message cross-posted to 'comp.datbase.mysql'
>>>
>>> I think Denis thought the question was relevant for c.d.m (maybe it
>>> should have been posted in the inactive group alt.php.sql).
>>>
>>>
>>>> it's a non-existing newsgroup......
>>>
>>> Just for your service provider don't supply that news group, id do
>>> exist.
>>>
>>>
>>
>> My newsgroup provider doesn't carry comp.database.mysql, either. It
>> does, however, carry comp.databaseS.mysql.
>
> I could try to blame it was still early in the morning for me... just
> didn't check the spelling that closely.
>
Moral of the story - don't reply before drinking at least 3 cups of
coffee :)
I've been known to do even stupider things before my morning coffee and
just before bedtime!
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]