Page 1 of 1
Question about PHP SQL Injection attack
Posted: Tue Sep 01, 2009 9:33 am
by davidonpda
Information is readily available regarding what this attack is, and how to prevent it, like using regex and adding slashes. Like this article here:
http://www.sitepoint.com/article/php-se ... lunders/2/
My question is, if you take the user inputted password on a form, and SH1 or MD5 it before sending it to the database, wouldn't that eliminate their injection attack?
Re: Question about PHP SQL Injection attack
Posted: Tue Sep 01, 2009 11:27 am
by flying_circus
davidonpda wrote:Information is readily available regarding what this attack is, and how to prevent it, like using regex and adding slashes. Like this article here:
http://www.sitepoint.com/article/php-se ... lunders/2/
My question is, if you take the user inputted password on a form, and SH1 or MD5 it before sending it to the database, wouldn't that eliminate their injection attack?
Sure, you can hash/encrypt the user's input, but that really only works for confidential data, like the users password. What about a username, email address, mailing/shipping address, etc?
That article seems as though it's a bit dated. I dont think I've worked on a server with magic quotes enabled in a long time, though I guess thats no substitute for not checking. Although adding slashes will work, most current popular databases have database specific escaping functions.
If your database is MySQL, the preferred method is to to use the newer mysqli extension which has mysqli_real_escape_string(). As I understand it, MySQL doesn't allow query stacking either. mysqli_real_escape_string() escapes most characters that have some sort of meaning to MySQL. For the wildcards like "%_" you can use a regex or the slower str_replace(). Well formed querystrings are also important.
Mordred has a really good SQL Injection article / howto floating around here somewhere. Search his name, I think his blog link is in his signature line.
This is a quick example of how I typically write my querystrings
Code: Select all
<?php
$user_name = $_POST['user_name'];
$querystring = sprintf("SELECT `user_id` FROM `users` WHERE `user_name`='%s';",
$mysqli->real_escape_string($user_name));
?>
*I use MySQL primarily, but I know PostgreSQL and a few other flavors have specific escaping functions as well.
Re: Question about PHP SQL Injection attack
Posted: Tue Sep 01, 2009 11:39 am
by davidonpda
Just reading to learn more. I use mysqli_real_escape_string() and sprintf() in my queries. Along with regex and strpos depending on what the actual database function is. I just was wondering, because some of the sites I have been reading, they specifically are talking about the password field and how vulnerable it is for logging in, but if you aren't storing the password in plain text, that first of all shouldn't be an issue.
Then additionally, if you are encouraging strong passwords, there is no reason a secure password couldn't contain ' - and whatever.
Thanks for your reply, helps me think I'm not always so crazy.
Re: Question about PHP SQL Injection attack
Posted: Wed Sep 02, 2009 5:20 pm
by phpGamer
Hi,
Somewhat related to the opening question:
1. I've read that use of prepared statements eliminates need to escape data to prevent SQL injection. Is this true or not?
2. If prepared statements eliminates need to escape data, then the only threat is using 'echo' or other commands that write unescaped data to the user or write unescaped data to the command line. Is this true or not?
(Example let's say I have $variable = $variable2; and now some douche sets data in database to oh say shell_exec('some bull') will the line in php now become $variable = shell_exec('some bull') and be parsed).
(Maybe code example would help would the following be vulnerable to SQL injection even though I do not escape data, do not encode and also I never display this information to user)
Code: Select all
/* PREPARED STATEMENT, BINDS AND DATA RETRIEVAL */
$stmt = mysqli_stmt_init($link);
if (mysqli_stmt_prepare($stmt, 'SELECT Login, Salt1, Salt2, Pass FROM users WHERE Login=?
LIMIT 1')){
// Bind Parameters
mysqli_stmt_bind_param($stmt, "s", $postLOG);
// Bind Result
mysqli_stmt_bind_result($stmt, $stmtLogin, $stmtSalt1, $stmtSalt2, $stmtPass);
// Execute Query
mysqli_stmt_execute($stmt);
// Fetch
mysqli_stmt_fetch($stmt)
$dbData['Login'] = $stmtLogin;
$dbData['Salt1'] = $stmtSalt1;
$dbData['Salt2'] = $stmtSalt2;
$dbData['Pass'] = $stmtPass;
}
Re: Question about PHP SQL Injection attack
Posted: Wed Sep 02, 2009 7:03 pm
by flying_circus
Point number 1 is true. Prepared statements (and I must admit, I'm really only a MySQL user so I dont now if its consistent with other flavors) basically accept a specific data type for each input varible. The Text data type interprets everything as a literal to alleviate problems of SQL injection. I was just reading this the other night in PHP Architect's - Guide to PHP security.
Re: Question about PHP SQL Injection attack
Posted: Fri Sep 04, 2009 11:21 am
by kaisellgren
Typical hash constructs contain hexadecimal numbers, so, they would eliminate the possibility for SQLi to happen. This does not mean it is a good idea to forget escaping. Escaping should be always done before inserting anything to the database and the escaping process should be done with database vendor specific functions/features (like mysqli_real_escape_string(), prepared statements) and not using some regular expressions.
phpGamer wrote:1. I've read that use of prepared statements eliminates need to escape data to prevent SQL injection. Is this true or not?
Prepared statements replace the need for escaping in DML statements. However, there are many ways for SQLi to happen even if you are using prepared statements. A simple limit for instance:
Code: Select all
if ($stmt = $mysqli->prepare("SELECT a FROM b WHERE c LIMIT ?"))
{
$stmt->bind_param("i",$d);
$stmt->execute();
...
$stmt->close();
}
Would be vulnerable to SQLi if $d is user supplied.
phpGamer wrote:2. If prepared statements eliminates need to escape data, then the only threat is using 'echo' or other commands that write unescaped data to the user or write unescaped data to the command line. Is this true or not?
Security is unfortunately not just about SQLi, XSS or some RCE, although XSS and SQLi together are the most popular types or attacks.
phpGamer wrote:(Example let's say I have $variable = $variable2; and now some douche sets data in database to oh say shell_exec('some bull') will the line in php now become $variable = shell_exec('some bull') and be parsed).
No... I think it would be wise for you to read about string data type.
Re: Question about PHP SQL Injection attack
Posted: Sat Sep 05, 2009 8:30 am
by phpGamer
Ah so its harder than that eh oh well

.
Thanks for the reply I thought I was going insane escaping everything. I was also doing it the wrong way totally with regex. So if I do the following:
1. htmlentities with ENT_QUOTES
2. my_sqli_escape_string
3. addcslashes %_
4. casting
5. prepared statement
along with whitelist input validation, is that enough to prevent sqli? Thanks.
Re: Question about PHP SQL Injection attack
Posted: Sat Sep 05, 2009 10:00 am
by kaisellgren
phpGamer wrote:So if I do the following:
1. htmlentities with ENT_QUOTES
2. my_sqli_escape_string
3. addcslashes %_
4. casting
5. prepared statement
along with whitelist input validation, is that enough to prevent sqli?
Do you know what's an SQL injection? Escaping and using prepared statements leads to double escaping. HTML entities are irrelevant to SQLi. See:
http://en.wikipedia.org/wiki/SQL_injection