sql injection

[draigGoch]

I have been given work to make sure that textboxes in a form can not be used for sql injection. The details in these textboxes are updating fields in a mySQL table.

I am new to this and didn't think about it at all until recently. Here is an example of what is going on:

<input type="text" name="myTextbox">

....

and then in the php

$sqlStr = "UPDATE myTable, SET field1='".$_POST[myTextbox]." WHERE ID=1";

In order for someone to hack this, don't they need a ' to close the "field1" variable? php automatically escapes this character with a \ . Is this enough or am I being really naive?

If this is not enough, would it be ok to use the mysql_real_escape_string function?

Thanks



A computer always does what you tell it to, but rarely does what you want it to.....

 

[thedaver]

if "myTextbox" had an incoming value of:

"blah blah; delete from users;"

You'd be very disappointed.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[draigGoch]

Yes, this is what I'm looking into now - like I said I'm new to this! But if they just entered:

blah blah; delete from users;

then would it not be:

$sqlStr = "UPDATE myTable, SET field1='blah blah; delete from users' WHERE ID=1";

therefore harmless in the string (maybe, hopefully!)?

A computer always does what you tell it to, but rarely does what you want it to.....

 

[Stretchwickster]

Sounds like you need to do a bit of reading, check these links out:

http://www.sitepoint.com/article/php-security-blunders/2

http://dev.mysql.com/tech-resources/articles/guide-to-php-security.html

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[jpadie]

yes but they could always insert a single quote.

however this would not work any way as mysql_query can not handle multiple queries in the same function call.

the best advice is:

1. test the input to make sure it is within the constraints of your business rules. so ensure that a telephone number looks like a telephone number etc etc.

2. ESCAPE all user input using mysql_real_escape_string(). You must be connected to the mysql server using mysql_connect() and have the db selected using mysql_select_db() before using mysql_real_escape_string().

3. enquote all string literals. I use single quotes for this and wrap the entire sql string in double quotes. I gather that most hardened professionals choose the opposite route.

so for a very simple approach ...

mysql_connect([  ], [  ], [ ]);
mysql_select_db([  ]);
$myFields = array ('name'=>'string', 'id'=>'integer');
$clean = array();
$err = array();
foreach ($myFields as $key=>val){
  switch ($val) {
      case 'string':
          $sql[] = "$key = '".mysql_real_escape_string($_POST[$key]) ."'";
          break;
      case 'integer':
          if(intval($_POST[$key]) == $_POST[$key])){
            //ok as an integer
            $sql[] = "$val = $_POST[$key]";
          }else{
            $err[$key] = 'not a proper integer']
          }
          break; 
  } 
}//endforeach

if (empty($err)){
    switch ($sqltype) {
     case "update":
      $sqlstring = "Update tablename set " . implode (',', $sql);
      break;
     case "insert":
      $sqlstring = "Insert into tablename set " . implode (',', $sql);
      break;
     case "select":
         $sqlstring = "Select * from tablename where " . implode (' && ', $sql);
         break;
     case "delete":
          $sqlstring = "Delete from tablename where " .implode (' && ', $sql);
         break;
  }
  mysql_query ($sqlstring) or die (myqsl_error());

[above typed directly into tt so check for syntax errors before using]

 

[draigGoch]

Thanks Stretchwickster. In the first link you sent, it read:

"To alleviate this problem, you need to escape dangerous characters from the user-submitted values, most particularly the single quotes ('). The simplest way to do this is to use PHP's addslashes() function.

But depending on your PHP configuration, this may not be necessary! PHP's much-reviled magic quotes feature is enabled by default in current versions of PHP."

My magic quotes feature is on, because when I post information ' turns into \' . Now what I want to know, is that is this enough?



A computer always does what you tell it to, but rarely does what you want it to.....

 

[thedaver]

draig,

I was specific in adding the trailing semi colon to my example. Using my example (with one amendment to add an appropriate "'" in the first stanza) you'd get the following SQL statements...

UPDATE myTable, SET field1='blah blah'; // works
delete from users; // works
WHERE ID=1 // incomplete, fails.

I like the reference links you were provided by Stretch...
plus:

http://www.developer.com/lang/article.php/922871

Read up on OWASP as well...




D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[thedaver]

I also forgot to link in:

http://www.owasp.org/index.php/PHP_Top_5

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[draigGoch]

Thanks for all your help guys. Christ! I didn't understand how serious / complex this was before!!!

Right, this is the conclusion that I have come to - correct me if I'm wrong (please!!!!):

1. My magic quotes feature is on so when posting XXX'XXX, it turns into XXX\'XXX .

2. Return mysql_real_escape_string($_POST[$myField]) when connection to db is open to the db.

3. Shout and scream at my own incompetence(!?!)




A computer always does what you tell it to, but rarely does what you want it to.....

 

[thedaver]

From the OWASP page:

There is some dissent in the PHP non-security community about the default behavior of PHP’s magic quotes, which “magically” adds slashes (not quotes) to input data on the basis that it might be destined for a database. As this behavior is not always available, code must be written with the assumption it will not be configured. OWASP’s issue with magic quotes is that it gives a false sense of security – it is not a silver bullet and it is insufficient to protect against advanced SQL injection techniques.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[draigGoch]

Ah, that makes sense thedaver, however the mysql_real_escape_string($_POST[$myField]) function escapes the single quote. So is this sufficient?

http://uk.php.net/mysql_real_escape_string

Thanks for all your help again



A computer always does what you tell it to, but rarely does what you want it to.....

 

[thedaver]

At the risk of being evasive, you need to determine "sufficient" within your own tolerance of risk.

Frankly, specifically stripping the "-", "'", and ";" characters outright would seem like an ideal start for working with MySQL data. Then you can encode, escape, whatever your text with a great deal more confidence.

That obviously depends upon the data fields you need to support and your desire to handcuff your users with certain data format constraints. My approach will certainly annoy anyone name "O'Callahan" or using a email domain at "example-domain.tld", but that's the point of understanding your data and the risks of safety vs. functionality.

Sorry to babble, I'm done.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[draigGoch]

You're not babbling - I need to learn!!!

The problem I have is that users need to type in a couple of sentences - therefore, stripping "-" "'" and ";" isn't functional in this context.

That's why I'm enquiring about the possibility of just escaping the characters using, say: mysql_real_escape_string().

Does anyone have any any simple innocent tests that I could type into these fields to see if my security is sufficient.



A computer always does what you tell it to, but rarely does what you want it to.....

 

[Stretchwickster]

This page (and the pages linked from it) may give you an idea:

http://sqlzoo.net/hack/

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096