Question about PHP SQL Injection attack

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
davidonpda
Forum Newbie
Posts: 3
Joined: Tue Sep 01, 2009 9:08 am

Question about PHP SQL Injection attack

Post 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?
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Question about PHP SQL Injection attack

Post 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.
davidonpda
Forum Newbie
Posts: 3
Joined: Tue Sep 01, 2009 9:08 am

Re: Question about PHP SQL Injection attack

Post 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.
phpGamer
Forum Newbie
Posts: 5
Joined: Tue Aug 25, 2009 11:12 am

Re: Question about PHP SQL Injection attack

Post 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;
}
 
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Question about PHP SQL Injection attack

Post 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.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Question about PHP SQL Injection attack

Post by kaisellgren »

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: <a class="linkification-ext" href="http://www.sitepoint.com/article/php-se ... lunders/2/" title="Linkification: http://www.sitepoint.com/article/php-se ... ders/2/</a>

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?
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.
phpGamer
Forum Newbie
Posts: 5
Joined: Tue Aug 25, 2009 11:12 am

Re: Question about PHP SQL Injection attack

Post by phpGamer »

Ah so its harder than that eh oh well :mrgreen:.

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.
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Question about PHP SQL Injection attack

Post 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
Post Reply