Re: [PHP] Malicious SQL
On Wednesday 07 July 2004 12:05, Keith Greene wrote: on the contrary: sql = mysql_query(select * from users where name='.$name.'); will simply look for a user with a name of Jim; delete from users; and return no results found. But I can also enter: jim'; delete from users You need to catch if there's a quote in the $name too, and escape that. RDB -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
on the contrary: sql = mysql_query(select * from users where name='.$name.'); will simply look for a user with a name of Jim; delete from users; and return no results found. But I can also enter: jim'; delete from users You need to catch if there's a quote in the $name too, and escape that. One thing to remember is mysql_query() will execute just one (the first) query so use of ; won't do anything in the above except break the query. Still though the point is well taken, be sure to add slashes (once) and put single quotes around the criteria ($name) and life will be grand. Quotes around numerical values won't hurt (such as id = '$id') although it's not required. If you choose not to do that then be sure it's numerical before use (like cast it as an int, or check is_numeric(), etc. ...). Some people check for ';' in the request variable and if found yell at the user, that can be fun. bugs.php.net does this. In regards to the controversial magic_quotes_gpc PHP directive, I feel it should remain on by default but if you know what you're doing then set it yourself. Scripts that work everywhere should of course work perfectly with it on or off. php.ini-dist (the default php.ini) has it on while the php.ini-recommended has it off. You must know what you're doing to use the 'recommended' version of php.ini. PHP is a newbie friendly language and newbies are for the most part clueless and don't know what strings or integers are, or why data should be escaped, or what data validation is or why it's important. This is why magical quotes exist as without them just think how many people would keep getting malicious SQL in their code and blame PHP, or how seemingly random SQL syntax errors would crop up. For these reasons dealing with Why do I get \' everywhere type questions is worth it, and why magic_quotes_gpc exists as a php.ini directive. Regards, Philip -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
In article [EMAIL PROTECTED], Philip Olson wrote: One thing to remember is mysql_query() will execute just one (the first) query so use of ; won't do anything in the above except break the query. If i'm not mistaken, newer versions of mysql do allow multiple queries. (once) and put single quotes around the criteria ($name) and life will be grand. Quotes around numerical values won't hurt (such as id = '$id') although it's not required. If you choose not to do that then be sure it's numerical before use (like cast it as an int, or check is_numeric(), etc. ...). Some people check for ';' in the request variable and if found yell at the user, that can be fun. bugs.php.net does this. Or use prepared statements with mysqli -- Tim Van Wassenhove http://home.mysth.be/~timvw -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
In article [EMAIL PROTECTED], Philip Olson wrote: One thing to remember is mysql_query() will execute just one (the first) query so use of ; won't do anything in the above except break the query. If i'm not mistaken, newer versions of mysql do allow multiple queries. I've heard the same thing, regarding MySQL 4. But someone else mentioned that this only applies to the command line of MySQL only. True? False? --Matthew Sims --http://killermookie.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
Philip Olson wrote: on the contrary: sql = mysql_query(select * from users where name='.$name.'); will simply look for a user with a name of Jim; delete from users; and return no results found. But I can also enter: jim'; delete from users You need to catch if there's a quote in the $name too, and escape that. One thing to remember is mysql_query() will execute just one (the first) query so use of ; won't do anything in the above except break the query. One should note that this isn't always the case for other databases, though. You can execute more than one query at a time with SQLite, for example. MySQL 4.1 allows more than one query at a time if you open the connection with a certain flag (in the C API at least), yet for security reasons, this flag wasn't included in the MySQLi extension. There is a multi_query() function/method, though. Hopefully you know what you're doing if you decide to use that function/method, though. PHP is a newbie friendly language and newbies are for the most part clueless and don't know what strings or integers are, or why data should be escaped, or what data validation is or why it's important. Yeah and we should turn register_globals back on by default, too!! ;) -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Malicious SQL
Can someone help me understand how people are able to use SQL maliciously if you don't protect against it in PHP? For example, I've written a very simple search SQL statement that takes the value of a variable for the search criteria ( from a webpage form ). I don't understand how someone could enter an SQL statement that could be malicious. Here's the SQL statement that I run once I have the search criteria stored in $strCriteria: SELECT autoQuesID, fldQuesTitle, fldBody FROM tblFAQ_Question WHERE (blnHidden = FALSE AND ((fldBody LIKE '%$strCriteria%') OR (fldQuesTitle LIKE '%$strCriteria%'))); I know in general that protecting against someone entering SQL is a must . So I guess I'm just wondering if anyone has any real-world experience with how people can take advantage of SQL and forms. Thanks! Gabe -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Malicious SQL
[snip] Can someone help me understand how people are able to use SQL maliciously if you don't protect against it in PHP? [/snip] This is a complex subject, but let us say that you didn't do checking at all, someone might be able to pass a delete statement in a form box like DELETE FROM `table` WHERE '1' = '1'. Since this statement returns TRUE the table would be emptied. There are many ways to combat SQL injections, the most basic of which have to do with things like GRANTS, query verification, data verification, etc. Read on ... http://www.google.com/search?hl=enie=UTF-8q=SQL+injection -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
For example, if you are not quoting your criteria: sql = mysql_query(select * from users where name=.$name); if someone enters the following in the name field, you're toast: Jim; delete from users; on the contrary: sql = mysql_query(select * from users where name='.$name.'); will simply look for a user with a name of Jim; delete from users; and return no results found. This is just one example. Your queries look fine. At 08:58 AM 7/7/2004, Gabe wrote: Can someone help me understand how people are able to use SQL maliciously if you don't protect against it in PHP? For example, I've written a very simple search SQL statement that takes the value of a variable for the search criteria ( from a webpage form ). I don't understand how someone could enter an SQL statement that could be malicious. Here's the SQL statement that I run once I have the search criteria stored in $strCriteria: SELECT autoQuesID, fldQuesTitle, fldBody FROM tblFAQ_Question WHERE (blnHidden = FALSE AND ((fldBody LIKE '%$strCriteria%') OR (fldQuesTitle LIKE '%$strCriteria%'))); I know in general that protecting against someone entering SQL is a must . So I guess I'm just wondering if anyone has any real-world experience with how people can take advantage of SQL and forms. Thanks! Gabe -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
SELECT autoQuesID, fldQuesTitle, fldBody FROM tblFAQ_Question WHERE (blnHidden = FALSE AND ((fldBody LIKE '%$strCriteria%') OR (fldQuesTitle LIKE '%$strCriteria%'))); Say $strCriteria comes from one of the values in the $_REQUEST array, a user change is coming through in that variable. so $strCriteria could contain something like %' OR fldBody = '' or fldBody LIKE '. Things like this would not be very harmful in your select statement but say it was a delete/update/insert statement and your $_REQUEST variable was some kind of primary key. They could delete your whole table, update your whole table, etc. Just make sure that data you get in is what you expect. here is a link that explains it better than I can http://www.sitepoint.com/article/794?ct=1 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
I have a question about this. Here is from the documentation: The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Why doesn't this automatically prevent injections, since it escapes out any single quotes they try to submit? - Brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
It may escape a quote, but injections would still be possible in other ways. It gets passed in as \' but then used normally as ' when it's the the variable. -Josh On Wed, 7 Jul 2004 10:31:17 -0700, Brian Dunning [EMAIL PROTECTED] wrote: I have a question about this. Here is from the documentation: The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Why doesn't this automatically prevent injections, since it escapes out any single quotes they try to submit? - Brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- -Josh -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
On Wed, 7 Jul 2004 10:31:17 -0700, Brian Dunning [EMAIL PROTECTED] wrote: I have a question about this. Here is from the documentation: The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Why doesn't this automatically prevent injections, since it escapes out any single quotes they try to submit? magic_quotes_gpc *should* fix all SQL injection attacks that come straight from $_GET, $_POST, and $_COOKIE, but it won't fix it from other places, such as in the DB or a session. In addition, if you use those $_GET, $_POST, or $_COOKIE values for other things, such as putting values back in a form or in HTML, you *will* get extra backslashes behind quotes. Basically you're looking at running removeslashes() on all places you use thos evalues except for SQL. The proper way to handle this is to turn off magic quotes and do whatever escaping your code needs *when it needs it*. For MySQL, use mysql_real_escape_string() (or addslashes() if you must). For HTML, use htmlentities(). etc. -- DB_DataObject_FormBuilder - The database at your fingertips http://pear.php.net/package/DB_DataObject_FormBuilder paperCrane --Justin Patrin-- -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
On Wed, 7 Jul 2004 10:31:17 -0700, Brian Dunning [EMAIL PROTECTED] wrote: I have a question about this. Here is from the documentation: The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Why doesn't this automatically prevent injections, since it escapes out any single quotes they try to submit? magic_quotes_gpc *should* fix all SQL injection attacks that come straight from $_GET, $_POST, and $_COOKIE, but it won't fix it from other places, such as in the DB or a session. In addition, if you use those $_GET, $_POST, or $_COOKIE values for other things, such as putting values back in a form or in HTML, you *will* get extra backslashes behind quotes. Basically you're looking at running removeslashes() on all places you use thos evalues except for SQL. The proper way to handle this is to turn off magic quotes and do whatever escaping your code needs *when it needs it*. For MySQL, use mysql_real_escape_string() (or addslashes() if you must). For HTML, use htmlentities(). etc. On top of that, the fact that you can turn it off creates different PHP environments between different servers. So now programmers are forced to check that magic_quotes_gpc is on each time and take appropriate action when it's off. I think a good programming habit it to treat it as if it were off. --Matthew Sims --http://killermookie.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
Brian Dunning wrote: I have a question about this. Here is from the documentation: The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Why doesn't this automatically prevent injections, since it escapes out any single quotes they try to submit? What if the SQL they inject doesn't have any quotes? Depending on how you create your SQL statement, magic_quotes_gpc may have no effect. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
Justin Patrin wrote: On Wed, 7 Jul 2004 10:31:17 -0700, Brian Dunning [EMAIL PROTECTED] wrote: The PHP directive magic_quotes_gpc is on by default, and it essentially runs addslashes() on all GET, POST, and COOKIE data. Why doesn't this automatically prevent injections, since it escapes out any single quotes they try to submit? magic_quotes_gpc *should* fix all SQL injection attacks that come straight from $_GET, $_POST, and $_COOKIE, No, it'll simple escape quotes in strings that are passed into those variables. If the injected SQL doesn't have any quotes and you're not quoting it in your SQL statement, then there's no protection. The proper way to handle this is to turn off magic quotes and do whatever escaping your code needs *when it needs it*. This is fine if you control every server you develop on, but most people can't do that. Write your own escaping function that detects the magic_quotes_gpc setting and act accordingly. Pass flags for whether your escaping the data for insertion into a database or for displaying in HTML. Turn off magic_quotes_runtime within your script, too. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
Matthew Sims wrote: I think a good programming habit it to treat it as if it were off. With regards to magic_quotes_gpc, you can't just treat it as if it were off otherwise you may end up escaping data twice if it's not really off on a server that runs your code. Detect it's value with get_magic_quotes_gpc() and act accordingly. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Malicious SQL
On Thursday 08 July 2004 00:05, Keith Greene wrote: Here's a simple (and probably quite common) example of how not checking user input will lead to disaster: DELETE FROM users WHERE userid = $userid userid is an integer column. If you didn't check your inputs and someone injected $userid = '1 or 1' you would have toasted your users table. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-general -- /* Klein bottle for rent -- inquire within. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php