Best way to prevent sql injection?

[mx2k]

does anyone have any methods that do not use sp (stored procedures) to prevent sql injection when dealing with php and mssql besides add slashes?

[dbevfat]

But that's a programming decision. The only values that should be casted to an int type are values that are ONLY allowed to be of an int type. "12 or 25" is a string value and should be escaped and quoted.

--ed

Well, that is obvious, isn't it?

But if a certain value is supposed to come in, and is expected to hold an int, you're going to cast it, right? And if someone send string "12 or 25" instead of an int, you're obviosly casting a string to int.

[samsm]

There is so much crossfire in this thread, I have doubts that anything in it can be considered obvious.

[Roger Ramjet]

There is so much crossfire in this thread, I have doubts that anything in it can be considered obvious.

Agreed. And so much pedantic point-scoring that I doubt it will be much use to anyone anymore. Best thing everyone can do is to stand back and review what has already been said before adding to the confusion. This is afterall a VERY IMPORTANT issue of concern to us all.

[coo_t2]

Well, that is obvious, isn't it?

But if a certain value is supposed to come in, and is expected to hold an int, you're going to cast it, right? And if someone send string "12 or 25" instead of an int, you're obviosly casting a string to int.

My personal opinion is that the DB code/queries shouldn't care what the user
input was, or how it was sent, etc.

Originally Posted by KTottE
SQL injection is a result of not validating input and/or not doing runtime type checks.

I have to disargee. Injection problem has absolutely nothing to do with validation. That is, 'valid' data can cause an injection, and vice versa.

I agree with sterefrog. Input validation and protecting your DB from
SQL injection are two different things. Or they should at least be looked
at as two different things.


I separate my DB code into DAOs from the rest of the app so that validating
input from the _REQUEST variables isn't even an issue. I don't see
it as the DB layer's responsibility.

Here's a partial example of one of my DAOs:

   
   <?php
   
   class CampaignsDao_Mysql extends CampaignsDao {
   
       
       function CampaignsDao_Mysql($dbConn)
       {
           parent::CampaignsDao($dbConn);
       }
   
   
       function addCampaign( $name, $groupID, $pay_per_x_visits, 
                          $cost_per_x_visits, $redirect_url=null )
       {
           if (!strlen($name) )
           {   
             $msg = 'Must provide campaign name as first parameter to '.         'CampaignsDao_Mysql::addCampaign.';
   
             return new DE_DataResourceError($msg, __LINE__, __FILE__);
           }
           elseif (!strlen($groupID))
           {
               $msg = 'Must provide a group ID as second '.
                  'parameter to CampaignsDao_Mysql::addCampaign.';
   
             return new DE_DataResourceError($msg, __LINE__, __FILE__);
           }
           elseif (!is_int($pay_per_x_visits) )
           {   
               $msg = 'Must provide an int value as third '.
                  'parameter to CampaignsDao_Mysql::addCampaign.';
   
             return new DE_DataResourceError($msg, __LINE__, __FILE__);
           }
           elseif (!is_numeric($cost_per_x_visits) )
           {   
               $msg = 'Must provide a numeric value as fourth '.
                  'parameter to CampaignsDao_Mysql::addCampaign.';
   
             return new DE_DataResourceError($msg, __LINE__, __FILE__);
           }
   
           // It doesn't necessarily have to be passed in as a float, 
           // but the DB stores it as a float.
           $cost_per_x_visits = (float)$cost_per_x_visits;
   
           $name = mysql_real_escape_string($name, $this->_dbConn);
   
           // $groupID is an alphanumeric string with length of 12
           $groupID = mysql_real_escape_string($groupID, $this->_dbConn);
       
           if ($redirect_url !== null)
           {
               $redirect_url = 
              mysql_real_escape_string($redirect_url, $this->_dbConn);
           }
   
           $campaignsTable = $this->_campaignsTable;
           
           $campaignID = parent::_get_rand_id(12);
   
           $query = "
               INSERT INTO $campaignsTable 
               VALUES ('$campaignID', '$name', $pay_per_x_visits,
                  $cost_per_x_visits, '$redirect_url') ";
   
           if (!$result = mysql_query($query, $this->_dbConn) )
           {   
               $msg = 'Error adding campaign in '.
                  'CampaignsDao_Mysql::addCampaign : '.
                       mysql_error();
   
             return new DE_DataResourceError($msg, __LINE__, __FILE__);
           }
   
           $joinTable = $this->_campaignsGroupsJoinTable;
   
           $query2 = "
               INSERT INTO $joinTable 
               VALUES ('$groupID', '$campaignID') ";
   
           if (!$result2 = mysql_query($query2, $this->_dbConn) )
           {   
               $msg = 'Error adding campaign in '.
                  'CampaignsDao_Mysql::addCampaign : '.
                       mysql_error();
   
             return new DE_DataResourceError($msg, __LINE__, __FILE__);
           }
   
           return $campaignID;
       }
   }
   
   ?>

Now I do some validation here, but really, the data should of been checked
before the method call was made. Just like any other method/function call, you have to make sure you feed it the values it wants.
I don't really see it as the DB layer's job
to validate data from the HTTP request. In terms of validation, the DB layer
only really needs to do what's necessary to do its job and protect itself.
It shouldn't really care what the application considers to be "valid" data. So if "12 or 25" is passed in when it is
supposed to be an int value, it shouldn't have even made it
to the point in your code that constructs a query string. So I guess my
point is, that input validation and SQL injection protection are two
different issues(as Stereofrog suggested earlier, you shouldn't confuse them) that should not be mixed together. Once you start looking
at them as the same issue, then you're data storage layer is having
too much influence over what your application code looks like.

--ed

[Archbob]

I found that turning Magic_quotes_gpc on and keeping on prevent almost all injections people try.

[shiflett]

I found that turning Magic_quotes_gpc on and keeping on prevent almost all injections people try.

This is a red herring. Magic quotes do not offer sufficient protection against SQL injection (which is simple to prevent), and they actually complicate a security-conscious developer's input filtering.

Therefore, I would argue that magic quotes decrease the security of an application rather than increase it.

[Archbob]

Magic quote prevent Delete statements, insertion statements, and most other stuff. Its kept the infidels at bay for my site these last couple of years.

There's also addslashes and mysql_escape_quotes.

[shiflett]

Magic quote prevent Delete statements, insertion statements, and most other stuff.

You really think so? :-) I think you'd be surprised what I could do to your database if you're relying on the escaping provided by magic quotes as your only protection. This is an extremely poor approach.

There's also addslashes and mysql_escape_quotes.

addslashes() is just the function magic quotes uses (well, magic_quotes_gpc, which is what most people refer to), so it has the same weaknesses. There is no such function as mysql_escape_quotes().

[dbevfat]

Magic quote prevent Delete statements, insertion statements, and most other stuff. Its kept the infidels at bay for my site these last couple of years.

Imagine a delete query:
DELETE FROM table WHERE record_id = [parameter]

where [parameter] is a $_GET parameter. Aside of other problems that might occur, you'd also face a danger of someone sending you "43 OR (1 = 1)" (without the quotes) as that value. Since no special char is used, the variable is not escaped with magic quoting. Actually, NO escaping would protect you here, you'd have to use some other aproach.

Regards

[ceefour]

Imagine a delete query:
DELETE FROM table WHERE record_id = [parameter]

where [parameter] is a $_GET parameter. Aside of other problems that might occur, you'd also face a danger of someone sending you "43 OR (1 = 1)" (without the quotes) as that value. Since no special char is used, the variable is not escaped with magic quoting. Actually, NO escaping would protect you here, you'd have to use some other aproach.

Regards

Does something like this has to be talked OVER AND OVER AGAIN?!?
We need something new here!!!

Anyway, just for being relevant, the solution to the 'quoted' post would be to use 'quoting' (as opposed to just "escaping", but in my world escaping is escaping+quoting).

And hey, has ANYONE ALREADY MENTIONED QUOTING AND ESCAPING?! No? Really? Check out the first page of this thread... If you can't find any of these two words then you should kill me.

I'm sick of this thread. This thread is evil. I'm unwatching it. I'm also evil though, so you should take this matter unpersonally and leave me alone.

[Archbob]

I've tried alot of delete queries, they don't work. I use htmlspecialchars and strip_tags in conjunction with magic quotes but I doubt that changes anything. I probably have some other database setting turned on.

[Daijoubu]

An int is an int, don't frigging quote it to make it a string :P
MySQL's query optimizer may go nuts with the indexes
Do the proper verification, is_numeric() won't hurt your fingers

Oh and it's even written here:
http://www.sitepoint.com/forums/showpost.php?p=396899
"Quotes around numeric data in queries"