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.

Using mod_rewrite to alter your dynamic URI’s

Ever wondered how WordPress sites get those nice search engine friendly URI’s? Whereby it changes http://www.example.co.za/?p=867 to something like http://www.example.co.za/contact-us?

Ever wondered how WordPress sites get those nice search engine friendly URI’s? Whereby it changes http://www.example.co.za/?p=867 to something like http://www.example.co.za/contact-us? Well it’s actually very simple to achieve and can provide great benefits to your site, with regards to both users and search engines. To modify your URI’s you need access to the .htaccess file as well as making sure that the rewrite module has been enabled on Apache on your server.

I am going to give a few examples as I know not all websites are built the same.

Example 1

<IfModule mod_rewrite.c>  
RewriteEngine on  
RewriteRule ^/yellow-bicycles$ /product.php?product=13
</IfModule>

This will essentially point you to the complicated URI by typing in the easy to read URI. As you can see this is already proving to be very handy.

Example 2

<IfModule mod_rewrite.c>  
RewriteEngine on  
RewriteRule ^/bicycles/yellow$ /product.php?product=bicycle&colour=yellow
RewriteRule ^/bicycles/red$ /product.php?product=bicycle&colour=red
RewriteRule ^/bicycles/blue$ /product.php?product=bicycle&colour=blue
</IfModule>

This now includes 2 variables and as you can see, makes things very simple and easy to read and understand.If for example you point your users to the following link:
http://www.example.co.za/products/bicycles/red
what its actually doing is pointing the user to:
http://www.example.co.za/product.php?product=bicycle&colour=red

Example 3

<IfModule mod_rewrite.c>  
RewriteEngine on  
RewriteRule ^products/([^/]+)/([^/]+)$ product.php?product=$1&colour=$2 [NC]
</IfModule>

This is where it gets REALLY useful. Here you can have one RewriteRule for numerous pages! It essentially takes the first variable and uses that for the product variable and the second for the colour. This can obviously be extended to suite your requirements. The [NC] at the end of the RewriteRule makes that rule case sensitive.

There are many more uses with the mod_rewrite module. I have just covered the basics here. Go out and give it a bash! Next time we’ll dive into Rewrite Conditions.

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.

PHP Header Include – Saving development time

I have had the opportunity to go through a lot of other developers coding skills and practices in the past few months and I must admit there is a lot of time wasting going on. Are you the type of developer that has a <title>, <meta>, <link> and <body> tag on every page without a global include? If so, let me show you how to simplify your life.

I have had the opportunity to go through a lot of other developers coding skills and practices in the past few months and I must admit there is a lot of time wasting going on. Are you the type of developer that has a <title>, <meta>, <link> and <body> tag on every page without a global include? If so, let me show you how to simplify your life.

By having a global header include you can set all your title tags in one central location as well as control which page gets what header information all in one file.

Firstly, create a file called header.php and use the below template:

header.php

<?php
	// Get the filename of the current page;
	$cpage = substr(strrchr($_SERVER['PHP_SELF'], "/"), 1);
 
	// set this to a "default" title as a backup incase you forget to mention a page. This stops it from returning nothing.
	$wtitle = "My Default Website Title";
 
	// Control page titles
	switch ($cpage) {
		case "index.php": $wtitle = "Home page title"; break;
		case "contact_us.php": $wtitle = "Contact us"; break;
		case "products.php": $wtitle = "Products Page"; break;
		case "about_us.php": $wtitle = "About us"; break;
	}
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head>
  	<title><?php echo $wtitle; ?></title>
  	<meta name="robots" content="index,follow,all" />
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
 
	<link rel="stylesheet" href="css/style.css" type="text/css" />
	<?php
		// if you're on the products page, load the products.css file.
		if ($cpage == "products.php") { echo '<link rel="stylesheet" href="css/products.css" type="text/css" />'; }
	?>
</head>
 
 
<body>
 
 
<div id="wrapper">

Now you can start creating all your other pages and simply include the header.php file right at the top as follows:

index.php / contact_us.php / products.php / about_us.php

<?php include "header.php"; ?>
 
<div id="content">
<p>Content goes here</p>
</div>
 
<?php include "footer.php"; ?>

As you can see we have taken this one step further and also created a footer.php file. This comes in very handy when you need to put tracking code at the bottom of ever page. Instead of pasting it on every single file, simply paste it into the footer.php.

footer.php

</div>
 
</body>
</html>

I hope you find this as valuable as I do. If you do it differently please give us the details. After all, we all learn from each other.