Re: [PHP] Re: sql injection protection
when we do b64e and then back b64d, you are saying. we get the org input all as clear text but this time as a string. because it is now a string, (which by definition can not be executed) what's the difference between b64e+b64d vs (string) casting then? if you were to cast the original input into string using (string), wouldn't you be in the same shoes? also on another note, if you know the userinput is in UTF-8, ( you verify that by running mb_detect_encoding($str, 'UTF-8', true); ), is there a situation where you think mysql_real_escape_string would fail in SQLINjection against string based user input ? The reason I ask this about specifically for strings is because it is fairly easy to validate againsts integers,floats,booleans using the built in validation filters my biggest issue is on strings... also what do you think about filter_sanitize_string. and finally, where do you think PHP community plus Rasmus is having a hard time implementing what you have in mind - that is a one liner that will do the inline string interpolation you are talking about.. what's the issue that it hasn't been done before? On Tue, Jan 24, 2012 at 1:45 PM, Alex Nikitin niks...@gmail.com wrote: You don't need to store it in the database as b64, just undo the encoding into your inputs for the purpose of the explanation, this is language independent b64e - encoding function b64d - decoding function pseudo code given: bad_num = ') union select * from foo --' bad_str = good_num = 123456 good_str = some searchable text the b64 way: bad_num=b64e(bad_num) ... good_str=b64e(good_str) inserts: query(insert into foo (num, str) values (b64d(\+bad_num+\), b64d(\+bad_str+\))); query(insert into foo (num, str) values (b64d(\+good_num+\), b64d(\+good_str+\))); Can you see that this will safely insert clear text into the database? This is because when you convert anything from b64, it will return from the function as a string and will not be executed as code... Now let's try a search: bad_num= '1 or 2 not like 5' bad_str = ' or \40oz\ like \40oz\ again we: bad_num=b64e(bad_num) bad_str=b64e(bad_str) then we can do a full text search: query(select * from foo where match(str) against(b64d(\+bad_str+\))) or even a number search query(select * from foo where num=b64d(\+bad_num+\)) again this is possible because no matter what you put in bad num, it will never be able to make post b64e bad_num look like code, just looks like junk, until b64d converts it to a string (which by definition can not be executed) make sense now? by check i mean, run utf8_decode for example... Problem is, that i can tell you how to write the most secure code, but if it's hard, or worse yet creates more problems than it solves (seemingly), nobody other than a few individuals with some passion for security will ever find the code useful. We need to fix this on the language level, then we can go around and tell programmers how to do it right. I mean imagine telling a programmer, that something that takes them 2 lines of code now, can be done much more securely in 5-7, and it creates code that doesn't read linearly... Most programmers will just ignore you. I want to say, hey programmer, what you do in 2 lines of code, you can do in 1 and make it impossible to inject into, then, then people will listen, maybe... This is where inline string interpolation syntax comes in, but it is not implemented in any programming languages, sadly actually. This is what i want to talk to Rasmus about. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
On 26-01-2012 15:46, Haluk Karamete wrote:
when we do b64e and then back b64d, you are saying. we get the org
input all as clear text but this time as a string. because it is now a
string, (which by definition can not be executed)
what's the difference between b64e+b64d vs (string) casting then? if
you were to cast the original input into string using (string),
wouldn't you be in the same shoes?
No, it's not. The problem here is that we're using 2 different systems,
which have to talk to eachother. They do this via strings.
If you send 'SELECT a FROM b', for PHP that's a string. It doesn't know
or even care if this is SQL or what you want to do with it. To PHP it's
just a string.
Once it gets to MySQL however, it will look at that string, parse it as
SQL and execute it.
Now, if we use:
'SELECT a FROM b; DROP TABLE b'
for PHP, it will still be just a string. Nothing special.
For MySQL however, it will have turned into 2 different operations,
which will both be executed. It will first SELECT a FROM b, and then
DROP TABLE b.
Can this be resolved by casting the whole query to a string in PHP? No.
It's already a string.
However, if you base64_encode a part of the query (the variable part
that you're afraid might get replaced by malicious code), it will appear
as a string to MySQL. It will recieve the following:
SELECT a FROM b WHERE c='MSc7RFJPUCBUQUJMRSBiIFdIRVJFIDE9JzE=';
instead of:
SELECT a FROM b WHERE c='1';DROP TABLE b WHERE 1='1';
To PHP, both are still strings. But to MySQL, the first is an operation
which SELECTs a from b where c has a certain value. The second, does the
same, but also drops the table (! WHOA! we Don't want that!!).
Of course, if we change the code to:
SELECT a FROM b WHERE
c=BASE64DECODE('MSc7RFJPUCBUQUJMRSBiIFdIRVJFIDE9JzE=');
It will select based on the STRING 1';DROP TABLE b WHERE 1='1 and will
not execute it, since it did not recieve it as executable code.
Do you finally understand the difference?
also on another note, if you know the userinput is in UTF-8, ( you
verify that by running mb_detect_encoding($str, 'UTF-8', true); )
This doesn't guarantee anything. You can't see the encoding on a bare
string. You can guess what it might be (using a function such as
mb_detect_encoding), but it might very well be wrong.
If I send you a string like 'abcdef', it may be detected as being ANSII,
ISO-8859-11, ISO-8859-16, and a million others. Why? Because encoding is
just a way of saying value X in this string represents character Y,
but to know that, you first need to know what codepage / encoding
belongs to it. If you don't know that, value 2148 might mean 'C' or 'F'
or 'PO'. You don't know, and you don't have any way of figuring this
out. That is why it is CRITICAL to know what encoding is being used.
If a UTF-7 encoded string is provided, it may look like a string of crap
to you. But when it is interpreted as being in UTF-8 it might suddenly
completely change meaning, and contain malicious code. The string itself
doesn't change at all, just the interpretation of the string.
When starting a connection, you should make sure that the encoding it
works with is the same you're using to construct your strings. So if
you're working in UTF-7, make sure MySQL is aswell. Otherwise, you have
to make sure to manually recode your strings from UTF-7 to UTF-8.
Hopefuly that makes it more clear to you.
, is
there a situation where you think mysql_real_escape_string would fail
in SQLINjection against string based user input ? The reason I ask
this about specifically for strings is because it is fairly easy to
validate againsts integers,floats,booleans using the built in
validation filters my biggest issue is on strings...
also what do you think about filter_sanitize_string.
and finally, where do you think PHP community plus Rasmus is having a
hard time implementing what you have in mind - that is a one liner
that will do the inline string interpolation you are talking about..
what's the issue that it hasn't been done before?
There are many ways of getting around the functions mentioned above.
Personally I have little experience with HOW you can do it (although
I've been forced to patch holes found due to the fact that we did rely
on it though). You can search the internet to find out how. We can't
really help you there, we can only advise you (as has been done a
million times already, though you don't seem to be able to accept the
recommendation). If you decide not to accept the recommendation, then
don't, and just use your own way. It may bite you after a while though.
Security issues like the one mentioned above are notoriously difficult
to eliminate effectively; oneliners rarely if ever suffice.
- Tul
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
On 01/26/2012 06:46 AM, Haluk Karamete wrote: when we do b64e and then back b64d, you are saying. we get the org input all as clear text but this time as a string. because it is now a string, (which by definition can not be executed) what's the difference between b64e+b64d vs (string) casting then? if you were to cast the original input into string using (string), wouldn't you be in the same shoes? Re-read his example. He encodes the data in PHP. But decodes the data in SQL. So, if you echo the SQL statement, you would see a base64 encoded string that SQL then decodes. also on another note, if you know the userinput is in UTF-8, ( you verify that by running mb_detect_encoding($str, 'UTF-8', true); ), is there a situation where you think mysql_real_escape_string would fail in SQLINjection against string based user input ? The reason I ask this about specifically for strings is because it is fairly easy to validate againsts integers,floats,booleans using the built in validation filters my biggest issue is on strings... also what do you think about filter_sanitize_string. read this: http://www.php.net/manual/en/filter.filters.sanitize.php Then read this: http://www.php.net/manual/en/filter.filters.flags.php It seems to me that filter_sanitize_string does not deal with anything other then ASCII. YMMV and finally, where do you think PHP community plus Rasmus is having a hard time implementing what you have in mind - that is a one liner that will do the inline string interpolation you are talking about.. what's the issue that it hasn't been done before? On Tue, Jan 24, 2012 at 1:45 PM, Alex Nikitinniks...@gmail.com wrote: You don't need to store it in the database as b64, just undo the encoding into your inputs for the purpose of the explanation, this is language independent b64e - encoding function b64d - decoding function pseudo code given: bad_num = ') union select * from foo --' bad_str = good_num = 123456 good_str = some searchable text the b64 way: bad_num=b64e(bad_num) ... good_str=b64e(good_str) inserts: query(insert into foo (num, str) values (b64d(\+bad_num+\), b64d(\+bad_str+\))); query(insert into foo (num, str) values (b64d(\+good_num+\), b64d(\+good_str+\))); Can you see that this will safely insert clear text into the database? This is because when you convert anything from b64, it will return from the function as a string and will not be executed as code... Now let's try a search: bad_num= '1 or 2 not like 5' bad_str = ' or \40oz\ like \40oz\ again we: bad_num=b64e(bad_num) bad_str=b64e(bad_str) then we can do a full text search: query(select * from foo where match(str) against(b64d(\+bad_str+\))) or even a number search query(select * from foo where num=b64d(\+bad_num+\)) again this is possible because no matter what you put in bad num, it will never be able to make post b64e bad_num look like code, just looks like junk, until b64d converts it to a string (which by definition can not be executed) make sense now? by check i mean, run utf8_decode for example... Problem is, that i can tell you how to write the most secure code, but if it's hard, or worse yet creates more problems than it solves (seemingly), nobody other than a few individuals with some passion for security will ever find the code useful. We need to fix this on the language level, then we can go around and tell programmers how to do it right. I mean imagine telling a programmer, that something that takes them 2 lines of code now, can be done much more securely in 5-7, and it creates code that doesn't read linearly... Most programmers will just ignore you. I want to say, hey programmer, what you do in 2 lines of code, you can do in 1 and make it impossible to inject into, then, then people will listen, maybe... This is where inline string interpolation syntax comes in, but it is not implemented in any programming languages, sadly actually. This is what i want to talk to Rasmus about. -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
Re-read his example. He encodes the data in PHP. But decodes the data in SQL. So, if you echo the SQL statement, you would see a base64 encoded string that SQL then decodes. Got it this time! Up until reading your reply, I was reading Alex's example with my pseudo-code glasses. I did not realize that the decoding was being done by SQL! I though it was still in PHP. And that's where I got confused with the hey why not string casting it then and got into what's the difference situation. But, you were laser sharp on that! Thanks a bunch! as to the other issue, the one with utf-8 and mb_detect_encoding, not working for it - cause there are ways of getting around. I still don't get it. First q comes to mind, why the heck use mb_detect_encoding then if it can be hacked around? see what I'm saying. but i don't want to go off on a tangent.. all i'm trying to do is to safely protect myself from a possible sql injection by using the available filters and sanitizations and techniques but without the PDO. That's the requirement. No PDO. From the earlier recommendations, I understand PDO is the way to go - cause it effectively separates the sql code from the user input to make sure user input does not get executed.. that explanation ... i get that... no problems there... yes, do use PDO... but my question is not what's the safest way in general?. But rather, what's the safest way without the PDO? Without the PDO, it seems like b64'ing it will do the job! And since the data will be stored as clear text, the searches against that data will also work too. I can take this implementation and build my library function based on that - instead of making it 1- first check if the in user string is in utf-8, 2- reject the input if not in utf-8 3- accept the input if utf-8 and apply the applicable filters to it starting with filter_sanitize_string 4- and on top of that, also mysql_real_escape it but from what i understand, you guys are saying just don't do this, because it may be overcome and that's not because of the fact filter_sanitize_string or mysql_real_escape_string is not effective, but because of the fact that there is NO WAY to reliably detect whether the incoming user input is in utf-8 or not. On Thu, Jan 26, 2012 at 9:14 AM, Jim Lucas li...@cmsws.com wrote: On 01/26/2012 06:46 AM, Haluk Karamete wrote: when we do b64e and then back b64d, you are saying. we get the org input all as clear text but this time as a string. because it is now a string, (which by definition can not be executed) what's the difference between b64e+b64d vs (string) casting then? if you were to cast the original input into string using (string), wouldn't you be in the same shoes? Re-read his example. He encodes the data in PHP. But decodes the data in SQL. So, if you echo the SQL statement, you would see a base64 encoded string that SQL then decodes. also on another note, if you know the userinput is in UTF-8, ( you verify that by running mb_detect_encoding($str, 'UTF-8', true); ), is there a situation where you think mysql_real_escape_string would fail in SQLINjection against string based user input ? The reason I ask this about specifically for strings is because it is fairly easy to validate againsts integers,floats,booleans using the built in validation filters my biggest issue is on strings... also what do you think about filter_sanitize_string. read this: http://www.php.net/manual/en/filter.filters.sanitize.php Then read this: http://www.php.net/manual/en/filter.filters.flags.php It seems to me that filter_sanitize_string does not deal with anything other then ASCII. YMMV and finally, where do you think PHP community plus Rasmus is having a hard time implementing what you have in mind - that is a one liner that will do the inline string interpolation you are talking about.. what's the issue that it hasn't been done before? On Tue, Jan 24, 2012 at 1:45 PM, Alex Nikitinniks...@gmail.com wrote: You don't need to store it in the database as b64, just undo the encoding into your inputs for the purpose of the explanation, this is language independent b64e - encoding function b64d - decoding function pseudo code given: bad_num = ') union select * from foo --' bad_str = good_num = 123456 good_str = some searchable text the b64 way: bad_num=b64e(bad_num) ... good_str=b64e(good_str) inserts: query(insert into foo (num, str) values (b64d(\+bad_num+\), b64d(\+bad_str+\))); query(insert into foo (num, str) values (b64d(\+good_num+\), b64d(\+good_str+\))); Can you see that this will safely insert clear text into the database? This is because when you convert anything from b64, it will return from the function as a string and will not be executed as code... Now let's try a search: bad_num= '1 or 2 not like 5' bad_str = ' or \40oz\ like \40oz\ again we: bad_num=b64e(bad_num) bad_str=b64e(bad_str) then we can
Re: [PHP] Re: sql injection protection
4 questions... which is basically all it comes to.. After all this back and forth emails, I think we should nail down these questions cause they are still not completely covered in my mind. question 1 If you use the PHP filters sanitizations, and you plan on using PDO with binded params, are you absolutely safe? And if not, why? What are the other ways for them to still make it in - even with PD0 and binded params properly in place? Just curious. question 2 If you use the PHP filters sanitizations, and for some reason, you CANNOT use PDO, what do you do against those situations where the user input is expected to be coming as a string and it's perfectly OK for it to be in say, around 1000 chars! For example, you are receiving a guest book comment. Use b64? But isn't with b64 search capability go down the drain? So we basically give up on search? Can we not come up with a solution which allows the search but yet still safe? What do we do? question 3 is there really no way to stop the user input's if char set is not utf8? Can we not enforce the userinput to be in UTF8 only and reject all input? If there is such a way, wouldn't we better of using mysq-_real_escape to allow both search and be safe? Or is there really no way to understand the incoming user input char set by PHP? question 4 do you have any white paper or any article that covers your most recommended solution against lengthy user input while you still want the search to work? you seem to know a lot and I think you should have at at least an article where we people can discuss the article at the bottom? It's always useful. If you don't have one, I strongly recommend you come up with one cause I'm sure it will be useful. On Mon, Jan 23, 2012 at 2:35 PM, Alex Nikitin niks...@gmail.com wrote: Start off with the fact that that article is from 2006, and its written by a programmer... I was simply asking expert opinion with the intention to learn. There is so much docs out there (I mean not just out there but at top security sites like owasp ) that recommends database specific escape solution as one of the viable alternatives. Escaping can work with a very specific set of circumstances, and it can be secure, however it fails as a security practice, and thus fails as a security solution. You make it seem like anyone who does not use PDO ( for one reason or another ), and rely on the mysql_real_escape_string can be by passed and SQL injected. I can't tell you for sure, however any project that uses it as their sole mean of sql injection protection can be exploited, yes. Just because OWASP says that it is a solution, doesn't mean that it's a good solution. Sometimes it's the only solution, yes, but it should not be the only security practice. So you're saying the mysql_real_escape_string() isn't 100% secure either? Crikey, if that's true, then I'm willing to bet A LOT of scripts are vulnerable to this problem. Any script that uses escaping as the sole means of protection, or doesn't do good checking, which is a lot of scripts. But i mean i hope it's no surprise, a lot of the web is vulnerable... Is there a fix that doesn't involve perpared statements? Perhaps a function that checks for this problem, and filters it? My charset/encoding knowledge is a bit limited, so I'd very much appreciate an answer. Thanks! Sure, i have already mentioned it... The glorious base 64 hack... Is it really that simple? It's hard to believe that all these implementations out there that honors the recommended filter database specific escape mechanisms would *easily* be vulnerable by simply someone sending ut7, is that what you are saying? A lot are... likewise UTF16, and even UTF8 can often be an issue. The issue with escaping is knowing what characters are bad, if you think you can escape a ' - tick and be safe, think again, in utf there are dozens if not hundreds of characters that can represent a tick in various circumstances. Again escaping fails as a security practice. Yes it can work and make your code uninjectable, but it still fails as a solution, even if secure... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
question 1 If you use the PHP filters sanitizations, and you plan on using PDO with binded params, are you absolutely safe? And if not, why? What are the other ways for them to still make it in - even with PD0 and binded params properly in place? Just curious. There are no known exploits or techniques on injecting into parameterized queries. question 2 If you use the PHP filters sanitizations, and for some reason, you CANNOT use PDO, what do you do against those situations where the user input is expected to be coming as a string and it's perfectly OK for it to be in say, around 1000 chars! For example, you are receiving a guest book comment. Use b64? But isn't with b64 search capability go down the drain? So we basically give up on search? Can we not come up with a solution which allows the search but yet still safe? What do we do? Search depends on your search, for example if i have 1000 chars, i may not want to search on all the words, only some key words, in which case b64 doesn't mean that you can't search. Doing full text index on a 1000char field in a decently large database can be quite hazardous to performance... On another note, you can still insert as clear text: insert into foo (bar, pub) values(b64d(c2hvdHM=), b64d(YmVlcg==)) it doesnt matter what is encoded in the b64, what matters is that it is NOT code that SQL will execute, you see what i'm saying? You can be decently secure with escaping, but again, it fails as a security solution. If you can do neither, then set the default char set on the page, database and even in php do a utf8_decode or something, validate, check, escape and you will be reasonably secure. question 3 is there really no way to stop the user input's if char set is not utf8? Can we not enforce the userinput to be in UTF8 only and reject all input? If there is such a way, wouldn't we better of using mysq-_real_escape to allow both search and be safe? Or is there really no way to understand the incoming user input char set by PHP? Set the default encoding on page and db, check in php question 4 do you have any white paper or any article that covers your most recommended solution against lengthy user input while you still want the search to work? you seem to know a lot and I think you should have at at least an article where we people can discuss the article at the bottom? It's always useful. If you don't have one, I strongly recommend you come up with one cause I'm sure it will be useful. I do not, however i am thinking about talking to Rasmus, to see if maybe i can get him to see the same issue with regards to the language that i am seeing, I'll go from w/e comes out of that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
My reply is in delims. question 1 If you use the PHP filters sanitizations, and you plan on using PDO with binded params, are you absolutely safe? And if not, why? What are the other ways for them to still make it in - even with PD0 and binded params properly in place? Just curious. There are no known exploits or techniques on injecting into parameterized queries. that's very good know. question 2 If you use the PHP filters sanitizations, and for some reason, you CANNOT use PDO, what do you do against those situations where the user input is expected to be coming as a string and it's perfectly OK for it to be in say, around 1000 chars! For example, you are receiving a guest book comment. Use b64? But isn't with b64 search capability go down the drain? So we basically give up on search? Can we not come up with a solution which allows the search but yet still safe? What do we do? Search depends on your search, for example if i have 1000 chars, i may not want to search on all the words, only some key words, in which case b64 doesn't mean that you can't search. Doing full text index on a 1000char field in a decently large database can be quite hazardous to performance... On another note, you can still insert as clear text: insert into foo (bar, pub) values(b64d(c2hvdHM=), b64d(YmVlcg==)) it doesnt matter what is encoded in the b64, what matters is that it is NOT code that SQL will execute, you see what i'm saying? You can be decently secure with escaping, but again, it fails as a security solution. If you can do neither, then set the default char set on the page, database and even in php do a utf8_decode or something, validate, check, escape and you will be reasonably secure. I'm not getting this one, in fact, I am totally lost in it. that's because I am not familiar with b64 encoding... look, I supplied the user input ; drop table members and then did a b64 on it, it gave me back OyBkcm9wIHRhYmxlIG1lbWJlcnM=. When I decode that back, I ended up with what I started that is ; drop table members. Knowing that, are you suggesting we do the following then? take the user input.example ; drop table members b64 it . now it's OyBkcm9wIHRhYmxlIG1lbWJlcnM= then store it like that in the db. and when you need to output back to screen, b64d to it ( that is b64 decode ) Well, If that's true, how do we allow user to search for say drop table for example? All you got in the db is OyBkcm9wIHRhYmxlIG1lbWJlcnM=. For a moment, I thought, if I do b64 on the user's search string that is drop table, search would work. But when I tested, the b64 of the partial string drop table is ZHJvcCB0YWJsZQ== and it is not part of the full string OyBkcm9wIHRhYmxlIG1lbWJlcnM=. Can you elaborate then how the searching would work? Any PHP sample may help where you used b64 trick in an implementation.. Otherwise, I'm still lost in this one. Also. when you say set the default char set on the page.. , do you mean this? meta http-equiv=content-type content=text/html; charset=UTF-8 and when you say set the default char set on the page.. , do you mean this? header(Content-Type: text/html; charset=UTF-8); Isn't page and php here in this context the same thing? In other words, isn't header(Content-Type: text/html; charset=UTF-8); produces on the resulting page meta http-equiv=content-type content=text/html; charset=UTF-8? What is that going to do to me if the user supplied the evil string right from the browser bar - where the referer is blank. I don't see how setting the default char set by page or php is of any help. And on the other hand when you say set the default char set on the database.. , do you mean to use SET NAMES 'utf8';? By doing so, we would make sure mysql_real_escape_string won't be get fooled. Is this understanding correct? question 3 is there really no way to stop the user input's if char set is not utf8? Can we not enforce the userinput to be in UTF8 only and reject all input? If there is such a way, wouldn't we better of using mysq-_real_escape to allow both search and be safe? Or is there really no way to understand the incoming user input char set by PHP? Set the default encoding on page and db, check in php when you say check in php, do you check it with mb_detect_encoding($str, 'UTF-8', true); Is that your way of checking too? And if you determine that it is UTF-8, are you completely fine with the mysql_real_escape_string for your non PDA insert/select/updates? yes/no. question 4 do you have any white paper or any article that covers your most recommended solution against lengthy user input while you still want the search to work? you seem to know a lot and I think you should have at at least an article where we people can discuss the article at the bottom? It's always useful. If you don't have one, I strongly recommend you come up with one cause I'm sure it will be useful. I do not, however i am thinking about talking to
Re: [PHP] Re: sql injection protection
You don't need to store it in the database as b64, just undo the encoding into your inputs for the purpose of the explanation, this is language independent b64e - encoding function b64d - decoding function pseudo code given: bad_num = ') union select * from foo --' bad_str = good_num = 123456 good_str = some searchable text the b64 way: bad_num=b64e(bad_num) ... good_str=b64e(good_str) inserts: query(insert into foo (num, str) values (b64d(\+bad_num+\), b64d(\+bad_str+\))); query(insert into foo (num, str) values (b64d(\+good_num+\), b64d(\+good_str+\))); Can you see that this will safely insert clear text into the database? This is because when you convert anything from b64, it will return from the function as a string and will not be executed as code... Now let's try a search: bad_num= '1 or 2 not like 5' bad_str = ' or \40oz\ like \40oz\ again we: bad_num=b64e(bad_num) bad_str=b64e(bad_str) then we can do a full text search: query(select * from foo where match(str) against(b64d(\+bad_str+\))) or even a number search query(select * from foo where num=b64d(\+bad_num+\)) again this is possible because no matter what you put in bad num, it will never be able to make post b64e bad_num look like code, just looks like junk, until b64d converts it to a string (which by definition can not be executed) make sense now? by check i mean, run utf8_decode for example... Problem is, that i can tell you how to write the most secure code, but if it's hard, or worse yet creates more problems than it solves (seemingly), nobody other than a few individuals with some passion for security will ever find the code useful. We need to fix this on the language level, then we can go around and tell programmers how to do it right. I mean imagine telling a programmer, that something that takes them 2 lines of code now, can be done much more securely in 5-7, and it creates code that doesn't read linearly... Most programmers will just ignore you. I want to say, hey programmer, what you do in 2 lines of code, you can do in 1 and make it impossible to inject into, then, then people will listen, maybe... This is where inline string interpolation syntax comes in, but it is not implemented in any programming languages, sadly actually. This is what i want to talk to Rasmus about. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
There is so much no, answers are in line.
At the top of each php page which interacts with a database, just have
this one liner
This has already been mentioned, but again, no, no connection if you
are not actually interacting with the database.
$DBH = safe_connection(database_name_here); //$DBH stands for
database handle
Another no, obfuscating away the user/pass doesn't make it a safe
function. Not saying there is no benefit to it, but where i would say
you would benefit is from making this into a singleton object for
example...
obviously the safe_connection is not a built-in PHP function so we
have to come up with it...
The idea behind this safe_connection function is this;
It takes the dbname, uses it in looking up to retrieve the database
username, the password, the host name and the hostname, and the host
type ( whether the host is mysql or mssql etc) - for the specified
database.
Shouldn't it also accept access type, for example i don't want to use
a user with input privileges if i am just looking stuff up in the
database... Also what year are we in? You do this, at least make it an
object so i dont need to remember what prefix i need to call...
Then it uses all this data to establish a db connection and thus get
the $DBHandle.
Yeah with an unknown type...
Once the $DBHandle is obtained, then mysql_real_escape_string ( or the
mysqli_real_escape_string version ) can be used
(However, the mentioned mysql_real_escape_string function here would
be the right choice **only if** the hosttype is mysql! ) So, that;s
where we use the hosttype. Microsoft SQL may require a different
escaping mechanism.
Did you not read anything i wrote above? Escape=fail... use a PDO
prepare and exec methods...
Now, the question is where do we use this mysql_real_escape_string function?
You DON'T!
Well, on the usual suspects! the dirty 5 arrays; namely _GET, _POST,
_COOKIE, _REQUEST and the _SERVER. Yes, the _SERVER too. ( that's due
to the http_referer, remote_addr etc spoofing ).
Here is a basic example handling the _GET array!
foreach ($_GET as $k = $v)
{
$_GET[$k] = mysql_real_escape_string($v); // this is good if
host type is mysql...
}
So, the basic idea is to clean up the entire GET array and be safe and
thorough. And do this across all global arrays where a user input can
possible come from.
No, no, owies, no... you don't want to escape everything, for one
thing, i can pass you anything i want to in get or post, including
100, or 10 8 meg files. You only use what you need out of the
arrays, ignore everything else
So, with this one liner function, called right at the beginning of
your script, you not only get a DBHandle to do your queries but also
get the assurance that the userinput is safe so you can get into
busines instantly as follows;
$safe_firstname = $_GET['firstname'];
How easy is that!
tail -n 1 | sed -i s/easy/horribly\sinefficient/
(To keep the basic idea short, I did not get into the magic_quotes_gpc
and stripslashes() matter. But I assume people reading this message
know whey are and how they get used.
So, if you just focus on the basic idea, what do you say? ARE WE STILL NOT OK?
Yes, All Your Base still Are Belong To Pen-testers!
Do we still need PDO?
If you haven't gotten it yet from my last 2 replies, YES
My answer to this question is ABSOLUTELY NO. But this NO is as far as
the SQLInjection woes. PDO may offer other advantages warranting its
use but as far as the SQLInjection is concerned and when we know that
the data has been thoroughly escaped like this, using PDO will not
make it any safer. Absolutely NOT.
Did you not read my last 2 replies, yes PDO will make it safer,
because escaping still FAILS! Another failure of your pseudo-code is
that it fails to go through a data-validation cycle
Do we all agree on that? It's a plain YES or NO question right here.
NO
As far as the C. Shifflet's article and Ilia's follow up post (
http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html
) is concerned, the only thing we need to worry about is whether we
are working with GBK character code, Chinese character set that is. If
we got nothing to do with GBK char set, then the technique I covered
above will suffice and cover us safely, conveniently and effortlessly.
But if you do work with GBK and you do that in your script by actually
running this ( mysql_query(SET CHARACTER SET 'gbk', $c); ), then the
above technique will doom you. Then PDO is your only bet, but
otherwise, we are OK.
no, no you are not...
As far as the escaping, I know you were against that. Here is what
you said about the escaping.
Oh hey, look, after many countless hours of researching the topic and
testing, and talking to other people who have done similar research,
and testing, and attending security conferences and writing papers for
developers of ISP-grade solutions, writing frameworks and
Re: [PHP] Re: sql injection protection
I was simply asking expert opinion with the intention to learn.
There is so much docs out there (I mean not just out there but at top
security sites like owasp ) that recommends database specific escape
solution as one of the viable alternatives.
You make it seem like anyone who does not use PDO ( for one reason or
another ), and rely on the mysql_real_escape_string can be by passed
and SQL injected.
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_3:_Escaping_All_User_Supplied_Input
http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string
http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html
quote from
http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html
So you're saying the mysql_real_escape_string() isn't 100% secure either?
Crikey, if that's true, then I'm willing to bet A LOT of scripts are
vulnerable to this problem.
Is there a fix that doesn't involve perpared statements? Perhaps a
function that checks for this problem, and filters it? My
charset/encoding knowledge is a bit limited, so I'd very much
appreciate an answer. Thanks!
#1 Dennis Pallett (Homepage) on 2006-01-22 14:08 (Reply)
As Ilia points out, it only applies to situations where the script
actually modifies the charset, for instance using SET CHARACTER SET.
Personally, I've never used this functionality and if you haven't
either you're fine.
#1.1 jome on 2006-01-22 14:48 (Reply)
That is precisely what the example demonstrates. The bottom line while
the problem is serious, it would only affect people changing character
sets from single-byte encodings to multibyte ones. As long as you stay
away from multibyte encodings, with the exception of UTF8 you should
be safe.
#1.2 Ilia Alshanetsky (Homepage) on 2006-01-22 15:15 (Reply)
/quote
I don't understand from what you say here...
When i send you something in UTF7, it will go through the escape as
utf7, since apache will push whatever i send into your vars, web
servers don't care about the char set, and PHP doesnt care what's in
the var either, especially in terms of a char set, so, it will hit
your database as utf7, which will change it to UTF8 for example or
whatever its default charset is...
Is it really that simple? It's hard to believe that all these
implementations out there that honors the recommended filter
database specific escape mechanisms would *easily* be vulnerable by
simply someone sending ut7, is that what you are saying?
On Mon, Jan 23, 2012 at 10:26 AM, Alex Nikitin niks...@gmail.com wrote:
There is so much no, answers are in line.
At the top of each php page which interacts with a database, just have
this one liner
This has already been mentioned, but again, no, no connection if you
are not actually interacting with the database.
$DBH = safe_connection(database_name_here); //$DBH stands for
database handle
Another no, obfuscating away the user/pass doesn't make it a safe
function. Not saying there is no benefit to it, but where i would say
you would benefit is from making this into a singleton object for
example...
obviously the safe_connection is not a built-in PHP function so we
have to come up with it...
The idea behind this safe_connection function is this;
It takes the dbname, uses it in looking up to retrieve the database
username, the password, the host name and the hostname, and the host
type ( whether the host is mysql or mssql etc) - for the specified
database.
Shouldn't it also accept access type, for example i don't want to use
a user with input privileges if i am just looking stuff up in the
database... Also what year are we in? You do this, at least make it an
object so i dont need to remember what prefix i need to call...
Then it uses all this data to establish a db connection and thus get
the $DBHandle.
Yeah with an unknown type...
Once the $DBHandle is obtained, then mysql_real_escape_string ( or the
mysqli_real_escape_string version ) can be used
(However, the mentioned mysql_real_escape_string function here would
be the right choice **only if** the hosttype is mysql! ) So, that;s
where we use the hosttype. Microsoft SQL may require a different
escaping mechanism.
Did you not read anything i wrote above? Escape=fail... use a PDO
prepare and exec methods...
Now, the question is where do we use this mysql_real_escape_string function?
You DON'T!
Well, on the usual suspects! the dirty 5 arrays; namely _GET, _POST,
_COOKIE, _REQUEST and the _SERVER. Yes, the _SERVER too. ( that's due
to the http_referer, remote_addr etc spoofing ).
Here is a basic example handling the _GET array!
foreach ($_GET as $k = $v)
{
$_GET[$k] = mysql_real_escape_string($v); // this is good if
host type is mysql...
}
So, the basic idea is to clean up the entire GET array and be safe and
thorough. And do this across all global arrays where a user input can
possible come from.
Re: [PHP] Re: sql injection protection
Start off with the fact that that article is from 2006, and its written by a programmer... I was simply asking expert opinion with the intention to learn. There is so much docs out there (I mean not just out there but at top security sites like owasp ) that recommends database specific escape solution as one of the viable alternatives. Escaping can work with a very specific set of circumstances, and it can be secure, however it fails as a security practice, and thus fails as a security solution. You make it seem like anyone who does not use PDO ( for one reason or another ), and rely on the mysql_real_escape_string can be by passed and SQL injected. I can't tell you for sure, however any project that uses it as their sole mean of sql injection protection can be exploited, yes. Just because OWASP says that it is a solution, doesn't mean that it's a good solution. Sometimes it's the only solution, yes, but it should not be the only security practice. So you're saying the mysql_real_escape_string() isn't 100% secure either? Crikey, if that's true, then I'm willing to bet A LOT of scripts are vulnerable to this problem. Any script that uses escaping as the sole means of protection, or doesn't do good checking, which is a lot of scripts. But i mean i hope it's no surprise, a lot of the web is vulnerable... Is there a fix that doesn't involve perpared statements? Perhaps a function that checks for this problem, and filters it? My charset/encoding knowledge is a bit limited, so I'd very much appreciate an answer. Thanks! Sure, i have already mentioned it... The glorious base 64 hack... Is it really that simple? It's hard to believe that all these implementations out there that honors the recommended filter database specific escape mechanisms would *easily* be vulnerable by simply someone sending ut7, is that what you are saying? A lot are... likewise UTF16, and even UTF8 can often be an issue. The issue with escaping is knowing what characters are bad, if you think you can escape a ' - tick and be safe, think again, in utf there are dozens if not hundreds of characters that can represent a tick in various circumstances. Again escaping fails as a security practice. Yes it can work and make your code uninjectable, but it still fails as a solution, even if secure... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
Haluk, don't listen to Ross, escaping fails, it was and is a bad solution to an old and still largely unresolved problem. The problem is and has been that of language interoperability, and we have been and continue failing at making a good way for languages to talk to each other, but because this is so needed, especially on the web, where you blink and you are in another language; php, css, html, throw in some javascript, and here is some SQL, oh i need some python, now let's throw in some C, but none of these languages talk to each other, so we have had to make it work and we do it with strings... This is why we have SQL injection and XSS, and the only, i will repeat that, the ONLY way to fix this issue is to have a clear way to say from language to language that this is a programmer string, run it, and this is user input, don't run it. The only right solution is to pass your code as code and the user input as user input, this way you are guaranteed that no execution of user input is possible via usual SQL injection or XSS means. Of course you still need to check and sanitize your input, there are still typical issues, buffer and heap overflows, etc, but simple inclusion of some special character and user input code that just gets ran just like programmer code is simply not possible. Escaping is a bad and many times failed attempt at saying that it's ok to pass user input as code, we just escape the characters we think are bad, to tell the interpreter not to execute them as it normally would. But what does it mean to be a character? Well back when all these languages were designed there was ASCII, and life was easy, now, however we have utf7, utf8, utf16, with tens of thousands of characters, many of which are the same symbol. Oh and they morph, if you don't know what best-fit matching is, look it up, but at the end of the day, if you think that you know what characters you need to escape, you are wrong, i'm sorry. This is why in javascript there are 3 escape functions: escape, escapeURI and escapeURIComponent. Which roughly translate to we failed, we failed again and we failed the third time. So in short, no, mysqli_real_escape_string is not a good solution to SQL injection, PDO (as far as i can tell, though i haven't poured over the code yet) or prepared statements, are. And neither negate the need to check your input, as other, more traditional exploits would still be possible (potentially) Oh Haluk, drop the idea of occurrences of words, it may stop someone who is just testing your code for fun, someone determined to get in will still do plenty of bad with whatever words you allow, and you have to allow certain words for your queries to run ;) Anyways, hopefully this is something for you guys to think about and hopefully enough to stop suggesting escaping as a viable option to stop any sort of simple code injection... ~ Alex -- The trouble with programmers is that you can never tell what a programmer is doing until it’s too late. ~Seymour Cray -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: sql injection protection
Thanks Alex. I re-read Ross's reply but I don't think you and him ( and even me ) is saying something different. It's all about validating the input. In my dictionary of programming vocabulary, validation is not just validating user input but also sanitizing it. You summed it up nicely by saying programmer's string vs user's string. It's the user's string where we want to focus on... If we go to the source of the problem that is receiving user input, that's where we start. So, every single user input ( not just query-string and form-post data or cookies but even the server variables such as the *http_referer must be considered as potential threats. If we are to store the user ip based on the server var http_referer value - without validating/sanitizing it - then we are really risking things! I Aren't we? So we should not think that hey that's a $_SERVER variable so why worry about it... I wanted to bring this matter up as a side parenthesis cause I don't think http_referer would be on many programmers' list of things to be watched out and to be sanitized! So first things first, we ned to identify all possible ways that a user input can take place. and that's not limited to querystings/form-posts and cookies. Server vars are in my list too, not all of them but those that have issues with. If you guys can think of some other types ( like the server variables that one would not think from the userland ) that I may be missing, I'd appreciate your filling in here. Once we know what possible places are out there that a user string can make it, we apply our function right there and then. So, yes, we need a library function that does this for us for once and for all. This library function must deal with data validation (on data length and on data type ) as its first line of defense. In that function, we should also do the escape! And that bring us to the 2nd line of defense. In this 2nd line of defense, we must take care of the char code specific issues. And for that it looks like mysql_real_escape_string() is my only line of defense. That's per this article http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string But it looks like, Alex you'd have a problem with that. Your reply sounded like that. Correct me if I got you wrong. If I have not misunderstood you, you have an issue with mysql_real_escape... But then could you tell me how you would deal with the GPK and GPK like matters in the light of Shiflett's article? And after that if the function is still returning OK, then I take that user input, use it in a PDO based implementation. Am I missing anything? Feel free to fill it in please so I can close this chapter and move on. On Tue, Jan 17, 2012 at 2:56 PM, Alex Nikitin niks...@gmail.com wrote: Haluk, don't listen to Ross, escaping fails, it was and is a bad solution to an old and still largely unresolved problem. The problem is and has been that of language interoperability, and we have been and continue failing at making a good way for languages to talk to each other, but because this is so needed, especially on the web, where you blink and you are in another language; php, css, html, throw in some javascript, and here is some SQL, oh i need some python, now let's throw in some C, but none of these languages talk to each other, so we have had to make it work and we do it with strings... This is why we have SQL injection and XSS, and the only, i will repeat that, the ONLY way to fix this issue is to have a clear way to say from language to language that this is a programmer string, run it, and this is user input, don't run it. The only right solution is to pass your code as code and the user input as user input, this way you are guaranteed that no execution of user input is possible via usual SQL injection or XSS means. Of course you still need to check and sanitize your input, there are still typical issues, buffer and heap overflows, etc, but simple inclusion of some special character and user input code that just gets ran just like programmer code is simply not possible. Escaping is a bad and many times failed attempt at saying that it's ok to pass user input as code, we just escape the characters we think are bad, to tell the interpreter not to execute them as it normally would. But what does it mean to be a character? Well back when all these languages were designed there was ASCII, and life was easy, now, however we have utf7, utf8, utf16, with tens of thousands of characters, many of which are the same symbol. Oh and they morph, if you don't know what best-fit matching is, look it up, but at the end of the day, if you think that you know what characters you need to escape, you are wrong, i'm sorry. This is why in javascript there are 3 escape functions: escape, escapeURI and escapeURIComponent. Which roughly translate to we failed, we failed again and we failed the third time. So in short, no, mysqli_real_escape_string is not a good
