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 <niksoft@xxxxxxxxx> 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. > > 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 100000 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 secure code, > and breaking all that; I summarized and articulated the problems with > interoperability of languages and failures of poor solutions to those > problem... Of course you know better. > >> Correct me if I'm wrong, >> >> IF WE ARE NOT CHANGING THE CHAR SET WITHIN THE SCRIPT USING SOMETHING >> LIKE "SET CHARACTER SET", THEN WHAT"S WRONG WITH ESCAPING WITH THE >> RECOMMENDED UTILITY FOR THE SQL HOST AT HAND? From Ilia's post, I >> understand that even if the character set was to be GBK at server's >> config level, we would be fine. Here is the excerpt from that very >> section. > > Facepalm. > > Don't scream. > > You have no choice in the matter. 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... And wait, > what just happened? > > > Also you make it seem like this is the recommended solution, fact is, > it is not recommended by either the PHP or the MySQL developers who > wrote those very functions. > > I have outlined why escaping fails, i have outlined how it fails, and > i have outlined how to go around it, i have outlined how it fails as a > security principle, i have outlined how to fix it. If you think you > have a more secure implementation than the one that just prohibits the > code injection possibility by eliminating the execution user-input > data, feel free to get owned... I am getting tired of repeating > myself, so if i have to say "escaping fails" one more time, i will > just start ignoring this thread -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
