SQL Injection

[The Little Guy]

I'm writing an SQL Injection Protection page, and I was hoping that you guys could look it over, and give me some thoughts/ideas/opinions/additions for my tutorial page.

 

http://phpsnips.com/help.php?hlp=SQL%20Injection&id=5

 

All help is appreciated.

 

Thanks!

[KevinM1]

Wouldn't it be better to validate the incoming requests at the source instead of playing games with sprintf() and a custom function to strip JavaScript?  I mean, neither would happen in the following case:

 

<?php
if(!preg_match("/^[A-Za-z ]+$/", $_POST['name'])){
   //bad data
}
else{
   $name = mysql_real_escape_string($_POST['name']);
}
?>

 

No numbers or special characters allowed in the example above.  Only letters and spaces.  This nullifies tags and the ' or 1=1-- business.

 

I'm sure someone with more experience will chime in (and tell me I'm wrong ), but to me, it just makes more sense to scrub data at the source by using regex to allow only that which matches the pattern(s) past the gate.  Then you can escape and insert it.

[Daniel0]

Nightslyr, if you're checking that the string only contains letters and spaces, then it's a waste running it through mysql_real_escape_string() seeing as it obviously does not contain any characters which should be escaped.

[KevinM1]

Nightslyr, if you're checking that the string only contains letters and spaces, then it's a waste running it through mysql_real_escape_string() seeing as it obviously does not contain any characters which should be escaped.

 

Ah, good point.  It's just a habit I've gotten myself into.

[The Little Guy]

In the case you provided Nightslyr, what if the user wanted to run a site, where HTML/CSS is an allowable input source? If that is the case, then your solution will not work, because it isn't allowing for HTML/CSS.

 

Your code also doesn't allow for punctuation, or numbers, a good function for what you have is this: http://php.net/ctype_alnum

[KevinM1]

In the case you provided Nightslyr, what if the user wanted to run a site, where HTML/CSS is an allowable input source? If that is the case, then your solution will not work, because it isn't allowing for HTML/CSS.

 

I assumed that you were talking about logins as that's what you call it in several places on the site.  Login, to me, is not a generic term you can substitute for form input/data input.  Login is a very specific instance of data input in which the user enters a username/e-mail address and password which gives them increased access to the site in question.  This might be trivial to you, but since you're writing a tutorial, you should be certain that the language used is as precise as possible.  Else, why use a term that isn't equivalent to that desired meaning (i.e. login != general data input).

 

I, for one, would not allow the public to submit HTML or CSS unless I was running a message board like this.  Then, yes (and obviously) I would modify my approach of scrubbing/validating user submitted data, although I would most likely still employ regex somewhere in the process.

 

Your code also doesn't allow for punctuation, or numbers, a good function for what you have is this: http://php.net/ctype_alnum

 

Yes, and that was somewhat intentional.  Since I thought you were mainly refering to login info, I made my example deal with a username (hence the $name variable and form field).  Obviously, most sites allow numbers for this, so a better regex would be:

/^[a-zA-Z0-9_]+$/

 

To account for the underscore character as well.

 

Here's another benefit to the regex method of form input validation: You not only ensure that the data is secure, you ensure that it's valid.

 

Yes, sprintf() will remove the threat of SQL Injection, but merely using that does not ensure that the form input data submitted is useful in any way.  Since you have to validate and secure the data anyway, why not do both at once?  Honestly, I can't think of a reason not to go the regex route.

[The Little Guy]

OK, it seems to me that I have some misguiding headings, would you make a suggestion on a better heading for the second portion of the article/tutorial, since that is no longer talking about logins, but the collection of user data, from either input fields or text boxes.

 

After saying that, I think a heading of: "Data Collection" could be a good heading, if you have something better, please let me know, thanks!

[KevinM1]

OK, it seems to me that I have some misguiding headings, would you make a suggestion on a better heading for the second portion of the article/tutorial, since that is no longer talking about logins, but the collection of user data, from either input fields or text boxes.

 

After saying that, I think a heading of: "Data Collection" could be a good heading, if you have something better, please let me know, thanks!

 

Hmm...I think that's a bit too general as you're specifically talking about someone inserting script tags (unless I'm missing the part you're referring to).  Not all data validation requires the programmer to strip just script tags from the input.  It's really more of an illustration of what damage a successful SQL injection can cause, which is good, IMO.  In fact, if possible you should make that a section in and of itself -- common SQL attacks -- which describe common attacks launched from a compromised database.

 

To get back to general data validation, you'd probably want to strip all tags from the input and perhaps even run the data through a profanity filter, especially if its a family friendly site.  If you want to allow tags, you should definitely add a part to the tutorial illustrating how to do it safely.  You should also check that inputed data is well-formed.  People don't tend to have digits in their names (i.e. John Smith II is valid, John Smith 2 is not), phone numbers don't tend to have letters, etc.

[Suchy]

This topic is just what I was looking for. I am writing a report on sql injection, so this site will be very helpfull.

 

Anyway could you guys recommend some good books, articles, websites, personal experience on the subject.

Thanks in advance

[KevinM1]

After doing some more research on this issue (mainly because I've never used sprintf() in my own code), I have a few more suggestions/comments:

 

sprintf(), from everything I've read and heard, doesn't really do anything to protect one from SQL injection by itself.  It does provide some typecasting, so if you have the following situation:

<?php
   $input = "5 or not 0";

   sprintf("INSERT INTO table (column1, column2) VALUES ($one, $two) WHERE something = %d",
              $input);
?>

 

Only the integer 5 is substituted into the query string, as it expects an integer (%d), not a string.  This doesn't provide any protection, though, if the query is indeed expecting a string at that location.

 

The key lies in using mysql_real_escape_string() to escape potential metacharacters, and in using regex to ensure that the data coming in is valid.

[Daniel0]

printf() is short for print formatted, i.e. it formats a string (so does the related functions in various manners). In itself it's not a tool for protection, but in your specific instance it does provide some level of security against SQL injections. %d signifies an integer which means that it will be substituted with and, if needed, converted to other an integer. Because integers do not pose any security risk regarding SQL queries, you have protected yourself from injections. Had you used %s, then you wouldn't have protected against anything at all.

 

You might want to look into prepared statements (see: http://php.net/manual/en/pdo.prepare.php).

[ardyandkari]

This topic is just what I was looking for. I am writing a report on sql injection, so this site will be very helpfull.

 

Anyway could you guys recommend some good books, articles, websites, personal experience on the subject.

Thanks in advance

 

if you google you will get oooooodles of info on sql injection.  how tos, how to protect, etc.

[KevinM1]

printf() is short for print formatted, i.e. it formats a string (so does the related functions in various manners). In itself it's not a tool for protection, but in your specific instance it does provide some level of security against SQL injections. %d signifies an integer which means that it will be substituted with and, if needed, converted to other an integer. Because integers do not pose any security risk regarding SQL queries, you have protected yourself from injections. Had you used %s, then you wouldn't have protected against anything at all.

 

You might want to look into prepared statements (see: http://php.net/manual/en/pdo.prepare.php).

 

Copycat.

 

Since you're here (so-to-speak), I was wondering how you'd go about securing textareas while allowing users to post code.  I look at a message board like this, which allows users to enter in both HTML and JavaScript (as well as just about everything else), and wonder how it's done without compromising security.  Does htmlentities() offer protection?  Or is it a combination of things?

[Daniel0]

printf() is short for print formatted, i.e. it formats a string (so does the related functions in various manners). In itself it's not a tool for protection, but in your specific instance it does provide some level of security against SQL injections. %d signifies an integer which means that it will be substituted with and, if needed, converted to other an integer. Because integers do not pose any security risk regarding SQL queries, you have protected yourself from injections. Had you used %s, then you wouldn't have protected against anything at all.

 

You might want to look into prepared statements (see: http://php.net/manual/en/pdo.prepare.php).

 

Copycat.

 

Heh... I read your post as a question, not as a statement. I see now I was mistaken. Sorry about that.

 

Since you're here (so-to-speak), I was wondering how you'd go about securing textareas while allowing users to post code.  I look at a message board like this, which allows users to enter in both HTML and JavaScript (as well as just about everything else), and wonder how it's done without compromising security.  Does htmlentities() offer protection?  Or is it a combination of things?

 

It depends on what you need to do. Generally a security issue consists of three things: an asset, a hacker and a vulnerability. All three has to be there... If there is no asset, then nobody bothers with you. If there is no hacker, then there is nobody to exploit your system. If there is no vulnerability, then there is obviously no way of exploiting it. What does this mean? It means that if all your users are completely trusted, then you don't have to bother with securing it and the level of trust should reflect the things users are allowed to enter into your textarea (or at least how it is represented when outputting it again). You could do various things. You could make a whitelist of HTML tags and attributes and convert everything which does not conform to that list to HTML entities. You could implement another language such as bbcodes. Generally though, if you're looking for just simple protection, then htmlentities() should do quite fine. When it gets complex (at least to some extent) is when you want users to be able to format the input but you do not trust them.

[Suchy]

Is it possible to attack a site's database through changing the url?

 

ex. if the url is something like: www.somesite.com/news.php?news=3

[KevinM1]

Is it possible to attack a site's database through changing the url?

 

ex. if the url is something like: www.somesite.com/news.php?news=3

 

I'm not sure if a direct attack (i.e., injection) using GET is possible.  It obviously depends on how the value is used, but in my experience, GET is typically used to retrieve database info, not set it.  That said, there is still some danger of indirect attacks.  There are two possibilities that immediately come to mind:

 

1. A site uses GET for user login.  Dumb, but it happens with newbies.  The link is something like: www.somesite.com/login?user=name&pass=pass.  One could then brute force their way onto the site as an administrator.

 

2. More likely, and dangerous, is the case of the lazy coder who uses the $_REQUEST array to handle everything instead of the separate $_GET and $_POST arrays.  So, even if user login is handled by a form that sends data through POST, since the script is using $_REQUEST, an attacker can keep feeding it modified URLs until they get in as admin.

[GingerRobot]

It doesn't really matter where the user input comes from, it's what is done with it that is important. It is entirely possible to attack a site's database through GET variables if the site has been poorly made.

[The Little Guy]

Some times it is a good idea to require post variables to come from your site, and no other.

 

if(preg_match("~^(http|https)://({$_SERVER['SERVER_NAME']})/.*~",$_SERVER['HTTP_REFERER'])){
echo 'Same DOMAIN!';
}else{
echo 'Wrong DOMAIN!';
}

 

The problem with the above method, is that HTTP_REFERER is set by the user agent. Not all user agents will set this, and some provide the ability to modify HTTP_REFERER as a feature.

 

I guess after that being said, if you are afraid of posts from the outside due to security problems, then you probably want to hack your site from another site to make it secure from outside sites.

 

You really wouldn't even have to do that if you validate every piece of code going to a database whether it be a SELECT, UPDATE, INSERT, DELETE, ALTER or any other types of queries. So, if your query expects a number, make sure that what is going in is a number, and not a string. If your expecting a string make sure there is not characters that will allow for database modification.

 

I don't know if this will help or not, but as I was typing this came to mind, couldn't one just use some sort of encode/decode function to insert the info into the database? this would help for two things.

1. there will be no quotes

2. the content is a little more private when someone views the database content.

[KevinM1]

Some times it is a good idea to require post variables to come from your site, and no other.

 

if(preg_match("~^(http|https)://({$_SERVER['SERVER_NAME']})/.*~",$_SERVER['HTTP_REFERER'])){
echo 'Same DOMAIN!';
}else{
echo 'Wrong DOMAIN!';
}

 

The problem with the above method, is that HTTP_REFERER is set by the user agent. Not all user agents will set this, and some provide the ability to modify HTTP_REFERER as a feature.

 

I guess after that being said, if you are afraid of posts from the outside due to security problems, then you probably want to hack your site from another site to make it secure from outside sites.

 

You really wouldn't even have to do that if you validate every piece of code going to a database whether it be a SELECT, UPDATE, INSERT, DELETE, ALTER or any other types of queries. So, if your query expects a number, make sure that what is going in is a number, and not a string. If your expecting a string make sure there is not characters that will allow for database modification.

 

I don't know if this will help or not, but as I was typing this came to mind, couldn't one just use some sort of encode/decode function to insert the info into the database? this would help for two things.

1. there will be no quotes

2. the content is a little more private when someone views the database content.

 

Yeah, the HTTP_REFERER would be perfect if it always worked. :\

 

Trying to harden GET is a bit tricky.  It's easiest when you know what values you'll allow to be passed into the system via URL.  Then, you can just create a regex that checks the value of the URL component vs. the values you'll allow.  Trying it the other way - storing a whitelist of commands/parameters and checking it against each key in $_GET - is a bit more complicated.  You could try something like:

<?php
   $safe = array("cmd", "page", "id"); //list of commands/parameters allowed to be passed into the system via URL
   $results = array();

   function isSafe($getInfo)
   {
      foreach($safe as $allowed)
      {
         for($i = 0; $i < count($getInfo); $i++)
         {
            $results[] = array_key_exists($allowed, $getInfo);
         }
      }

      return $results;
   }

   //...

   if(isset($_GET) && isSafe($_GET))
   {
      $safeGetResults = isSafe($_GET);
      //handle the results and process
   }
?>

 

It's not particularly elegant, and I'm sure someone else can come up with a better way of doing it, but the idea is there.

 

Regarding the exchange of database info: for simple encoding/decoding, you could probably switch to Base64 and back, or uuencode, but that would result in larger values being saved in the database.  If the database is storing critical info, there's probably a way to encrypt/decrypt it.  Does base SQL have encryption built in?  Like an AES function or something?

[KevinM1]

D'oh, the function should be array_keys(), not array_key_exists().