Stopping SQL Injections

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed.

SQL Injections can leave your website crippled and useless and most developers haven’t even thought about this. Do you know what a SQL injection is? Here is the official definition:

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.

How they do it
SQL injections can be inserted through the use of your website’s forms as well as your global variables. An attacker would analyse your forms and attempt to manipulate the way you insert data into your database.

Example:
Lets say one of your pages is as follows:
http://examples.co.za/product.php?product=salt
And the attacker adds ‘ or ‘a’=’a to the end of the URI like such: http://examples.co.za/product.php?product=salt’ or ‘a’=’a
What this is essentially doing is changing your unprotected SQL query to something like
SELECT * FROM product WHERE product=’salt’ or ‘a’=’a’
Instead of the query now looking for products that equal ‘salt’, it now selects everything regardless! By using this in a log in form the attacker may be able to gain access to the site without actually logging in. This is an extremely easy method of manipulating your query. There are a lot more malicious techniques out there.

What to do
If you are using a solid framework you are relatively safe but in actual fact, SQL injections are hard to stop as there are many ways to pull this off. Here are some steps you can take to ensure some heartless moron doesn’t come along and wipe your database off the face of the earth or worse, gain access to passwords you don’t want them to know about and wreak more havoc.

Sanitizing Function
This simple yet effective sanitizing function escapes special characters in a string for use in a SQL statement.

<?php
  function sanitize_slash($string) {
    // make sure you are connected to your DB before attempting to return this function
    return mysql_real_escape_string($string);
  }
?>

The above method will add a ‘\’ to single and double quotes to ensure your query cannot be manipulated. The next method eliminates ALL special characters except for a-z, A-Z and 0-9. Your choice is dependent on your websites functionality.

<?php
  function sanitize_all($string) {
    return preg_replace( "/[^a-zA-Z0-9 ]/i", "", $string );
  }
?>

For example, if you would like to sanitize the input of a text field, use the first function. If you would like to sanitize the input of a users username, use the second function.

Example:

<?php
  $aboutme = sanitize_slash($_POST['website']);
  $firstname = sanitize_all($_POST['firstname']);
  $surname = sanitize_all($_POST['surname']);
  $email = sanitize_slash($_POST['email']);
  // you should get the picture now...
?>

Another good method to use in conjunction with these functions is to check if the given input is the expected data type. IE: If you are expecting a number format, double check it with the is_numeric() function.

One last thing to remember, make sure you turn off error_reporting(); The last thing you need is to show the attacker the database error details!

<?php error_reporting(1); //add this right at the beginning of your file ?>

So in closing, make sure you follow these steps to try and eliminate any potential attacks that may occur:

  • Never trust user input.
  • Sanitize your variables before attempting to insert them in a SQL query.
  • Make sure your form < input > names are not the same as your table’s field names.
  • Make sure you double check expected data types.
  • Turn off error_reporting()

Good luck and as always, if you have anything to add to this, please feel free to insert your suggestions or examples below.

Maximum MySQL Database Size?

While developing myScoop, and other experimental projects, I have been thinking about the limitations of a MySQL database, particularly it’s maximum file size. I was amazed that after some research I really have absolutely nothing to worry about. It seems my 112mb database is a “new born baby” in the terms of how big it can still grow.

MySQL LogoWhile developing myScoop, and other experimental projects, I have been thinking about the limitations of a MySQL database, particularly it’s maximum file size. I was amazed that after some research I really have absolutely nothing to worry about. It seems my 112mb database is a “new born baby” in the terms of how big it can still grow. Here are the estimated maximum file sizes per operating system:

Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/ HFS+ 2TB
NetWare w/NSS file system 8TB

This information was taken directly off MySQL.com but this is not necessarily the maximum limitations of your database. A number of methods can help to increase your max file size:

LFS (Large File Support) in Linux
To support files larger than 2GiB on 32-bit Linux systems you would have to use LFS. Although I’m sure the latest operating systems would come out with this already enabled. The standard max file size limitations without LFS enabled are 2^31 bytes(2GiB), but enabling LFS can enable your maximum file size to reach 2^63 bytes (9 223 372 036 854 775 808 bytes). Crazy, I know!

Using the “Alter Table” command
This will come in handy when using the MyISAM storage engine. The simple “Alter Table” in the mysql prompt command can extend your database capacity dramatically.
Example: “alter table ‘weather’ max_rows = 200000000000”
Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)

Most of this researched information is very old so I decided to run a little check (which I should have done right in the beginning), and by doing so on my local machine, I literally nearly fell of my chair:

mysql> show table status like ‘blog_hits’ \G
*************************** 1. row ***************************
Name: blog_hits
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 15497
Avg_row_length: 247
Data_length: 3835852
Max_data_length: 281474976710655
Index_length: 366592
Data_free: 0
Auto_increment: 15509
Create_time: 2009-11-24 16:53:38
Update_time: 2009-11-24 16:53:38
Check_time: 2009-11-24 16:53:38
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

This is telling me my max database size can be as much as 281474976710655 bytes which If I’m not mistaken, equates to 256 terabytes. With this being said, I think we are going to run into system limitations rather than MySQL limitations, so therefore, revert back to the table at the top of this post.

For more information on this topic,  Kristian Köhntopp provides more of a technical answer to the above question.