Category Archives: SQL

Backup MySQL Databases

If you are running a database driven website then your data sitting in MySQL database id most important for you. In most of shared hosting there is no provision for scheduled backup of databases. So I have written a script to take backup of all databases associated with the account.

SQL Injection Prevention

One of the things that most scared me when creating database driven applications with PHP was the thought of someone messing with my database by using SQL injection attacks. I had heard about this from various times before thanks to other sites, but didn’t really know what it meant or what harm it could cause. In this article, I hope to enlighten you.

The content and/or the techniques used in this post may be out of date. Because of this, please take extra care when using the content. If in doubt, please contact an administrator.

Knowledge Required

  • Basic knowledge of using SQL databases with PHP.
  • Knowledge of how the $_GET or $_POST global array is used in constructing SQL statements.

What is an SQL injection attack?

When you visited a page like http://example.com/page.php?id=101 an SQL statement like the following was constructed.

SELECT * FROM sometable WHERE ID = ‘101’;

As you most likely know, this pulls the record from the database where the ID field is equal to 101. There is nothing insecure about that statement and it will work perfectly fine. You can change the value on the end of the URL to modify the query and pull the new data from the database. Ah, there’s your problem, in that last sentence. The part that said that you could change a value and it would modify the query. Now thats fine if you’re simply changing it to another post number, but what if someone malicious decided to put something else in there? What happens if they replace it with the following text (notice the apostrophe): 114′ ; bad stuff here ‘23? That would then make the query look like the following.

SELECT * FROM sometable WHERE ID = ‘yaywoo'; bad stuff here ’23′;

Whats happened there is that we’ve closed the string yaywoo by placing an apostrophe there. This allows us to enter any other SQL statements we like (think DROP TABLE). Obviously this is a large problem as we can’t have our users free to delete our tables. The way we prevent this is by running a few “filters” on the string that is passed in through the $_GET array.
How do I prevent them?

Preventing them is actually very simple, all you need to do is to “filter” and validate the input that the user has given us. Remember, you should never trust your users. If you keep that in mind when developing your applications, you’ll be just fine (also assuming you know how to prevent them, which I will get on to). Its annoying how easy it is to prevent them, yet so many people are still coming home to see that their tables have been deleted, or their sites been spammed with lots of new pages or something. If you have just started developing using databases, you’re probably quite familiar with seeing the following type of code.

//Connect to the database

$dbh = mysql_connect(‘localhost’, ‘root’, ‘password’);

//Select the database
mysql_select_db(‘content’, $dbh);

//Construct the query
$SQL = “SELECT * FROM sometable WHERE ID = ‘”.$_GET[‘id’].”‘”;

//Send the query and grab the result set
$result = mysql_query($SQL, $dbh) OR die(mysql_error($dbh));
$items = mysql_fetch_array($result);

//Just show the array quickly
print_r($items);

//Close the connection to the database

mysql_close($dbh);

The problem with this code is that the $_GET value ‘id’ has no filtering or sanitising before it goes into the query, as shown earlier. What we need to do is make sure that the data entered is going to be safe to enter the database. We can do this by using the following few methods.
Using mysql_real_escape_string();

This is the best way to prevent nasty characters entering the database. What this does is search through the string and prepends a backslash to the following: x00, n, r, , ‘, ” and x1a. Adding a backslash makes MySQL treat the character after it as a comment and therefore will ignore it. You can use this function like this:

//Database connection has already been made previously.

$id = mysql_real_escape_string($_GET[‘id’]);

//Construct the query
$SQL = “SELECT * FROM sometable WHERE ID = ‘”.$id.”‘”;

//Send the query and close the connection to the database

By always using this, you’re pretty much safe from SQL injection attacks. Theres also one other thing that you need to do. If you are in a situation where say, you added some article text to the database and used mysql_real_escape_string() on it when it was entered, when you grab it out of the database to display again, it will have backslashes in front of all the quotation marks that might be in it. This is pretty annoying to read, so you might want to remove the slashes just before displaying them. You can do this by using a function called stripslashes()

//Database connection has already been made previously.

$id = mysql_real_escape_string($_GET[‘id’]);

//Construct the query
$SQL = “SELECT * FROM sometable WHERE ID = ‘”.$id.”‘”;

//Send the query and fetch the results
$result = mysql_query($SQL);

//Cycle through the posts in the database and echo the titles.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
//Strip the slashes from the r
esult

$postTitle = stripslashes($row[‘postTitle’]);
echo ‘Post Title: ‘ .$postTitle;
}

//Free the result set from the memory
mysql_free_result($result);

//Close the database

Important note!

There could be a problem with using this function if your host has enabled something called get_magic_quotes_gpc(). What this does is automatically escape quotes and backslashes from all $_POST and $_GET data. This causes a problem, as when you run it through mysql_real_escape_string(), it will cause the backslahes put in by magic quotes to be escaped again. We don’t want this. What we have to do is check if this babysitting function is turned on, and if it is, use the stripslashes() function that we met earlier to remove the slashes added by get_magic_quotes_gpc(). You can do this by using the following code:

//Database connection has already been made previously.

//Grab the data from the $_GET array
$id = $_GET[‘id’];

//Check to see if magic quotes is enabled, and if it is, strip the slashes added by it.

if (get_magic_quotes_gpc()) { $id = stripslashes($id); }
$id = mysql_real_escape_string($id);

//Construct the query
$SQL = “SELECT * FROM posts WHERE postID = ‘”.$id.”‘”;

//Send the query and fetch the results
$result = mysql_query($SQL);

//Close the database

Numeric validation

Its good practice to check if the type of data being entered is the type of data that you want, this can prevent attacks happening in the first place, before the query is even built. There are certain functions in PHP that can help with this. Ok first example, say that you’re passing the article ID number in to grab the article from the database. It is pretty obvious that you are only ever going to have numeric data being entered, so why not limit the data only to numeric. You can do this by using the following code:

//Database connection has already been made previously.

//Grab the data from the $_GET array
$id = $_GET[‘id’];

//Check to see if it is not numeric (the ! mark inverts the)
if (!is_numeric($id)) { die(‘Please do not modify the article ID’); }

//Construct the query
$SQL = “SELECT * FROM sometable WHERE ID = ‘”.$id.”‘;”;

//Send the query and fetch the results
$result = mysql_query($SQL);

//Close the database

There are many other validation checks like this. Here are others you can use:
is_ array
is_ bool
is_ callable
is_ double
is_ float
is_ int
is_ integer
is_ long
is_ null
is_ numeric
is_ object
is_ real
is_ resource
is_ scalar
is_ string
isset

After looking at this list, it would have probably been a better choice to use is_integer() rather than is_numeric() as the article ID will never be a decimal number (at least I hope not). It is always a good idea to validate user input like this as well as securing the data.
Length Validation

Checking the length of the data is a quick and easy way of telling whether the user is genuine or not and can prevent you sending the query to the database just to receive an error. There are two main ways of doing this task, and it depends on the data type. If you have text data (strings) then you need to check the length of the string (the amount of characters in it), whereas with numbers, you can check whether they are in a certain valid range. To find the length of string you can do the following:

//Database connection has already been made previously.

//Grab the data from the $_GET array
$username = $_GET[‘username’];

//Get the length of the string
$length = strlen($username);

//Check that the string length is within a valid range (5 to 25 characters, I’m validating a username).
if ($length < 5 || $length > 25) { die(‘Please enter a username between 5 and 25 characters long’); }

//Construct the query
$SQL = “SELECT * FROM users WHERE username = ‘”.$username.”‘”;

//Send the query and fetch the results
$result = mysql_query($SQL);

//Close the database

It is a quicker process to validate numeric input. In this example, I am validating that an article ID is between 1 and 10,000.
//Database connection is present

//Get the id from the $_GET array
$id = $_GET[‘id’];

//Validate between the ranges 1 and 10,00
if ($id < 1 || $id > 10000) { die(‘Please enter a valid artile ID’) }

//Construct the query
$SQL = “SELECT * FROM sometable WHERE ID = ‘”.$id.”‘”;

//Close the connection to the database

One thing more i want to discuss here is that if you have te take text input do convert following symbols as follows:

&
&amp;
<
&lt;
>
&gt;
&quot;
&apos;
(
(
)
)
#
#
%
%
+
+
-
-
;
;


Putting it all together

Ok, so I’m going to oversecure a query to the database that selects an article by using the given article ID. Here is the code.
//Database connection is present

//Make sure that the id is actually given

if (isset($_GET[‘id’]))
{ $id = $_GET[‘id’]; }
else { die(‘Please provide an article ID’); }

//Make sure that its an integer
if (is_integer($id)) { die(‘Please enter a valid article ID’) }

//Validate that its in between the ranges 1 and 10,000
if ($id < 1 || $id > 10000) { die(‘Please enter a valid artile ID’) }

//Construct the query
$SQL = “SELECT * FROM posts WHERE postID = ‘”.$id.”‘”;

//Send the query and close the connection to the database

This next one will validate a username before its entered into the database.

//Database connection is present

//Make sure that the id is actually given
if (isset($_GET[‘username’])) { $username = $_GET[‘username’]; }
else { die(‘Please provide a username’); }

//Get the length of the username
$length = strlen($username);

//Validate
t < 3 || $length > 20)
{ die(‘Please enter a username between 3 and 20 characters long’) }

//Make sure that its safe to enter the database.
$username = mysql_real_escape_string($username);

//Construct the query
$SQL = “SELECT * FROM username WHERE username = ‘”.$username.”‘”;

//Show the username
echo ‘Username: ‘.stripslashes($username);

Conclusion

A few things to remember about securing data for entering into an SQL query.
Never trust your users
Always validate input and if necessary, output
On a production server, never show the SQL query when an error occurs, this will give the cracker the advantage.