PHP SQL Security

It came to my attention a few years ago, that SQL code could easily be injected into forms that interact with a database. This can cause many problems.

If the user’s input is not validated for “dangerous” characters, it could lead to your entire database table being deleted (”dropped”).An example would be if you are using dynamic URLs. You use the user input in the URL to select a record from a database and display it. Say you have navigation like this… page.php?id=14. Then in your code, you have something like this.

//Connection to the database present.
//---------------------------------------------
//Get the "id" variable from the URL submitted by user.
$id =  $_GET['id'];
 
//Select the record from the database where the id
$query =  "SELECT *
FROM content
WHERE id = '$id'";
 
//Query the database.
$result =  mysql_query($query);
 
//---------------------------------------------
//Display the page.
?>

This example is completely unsecured. The “id” variable is being taken directly from the user, and being entered into a SQL query without any precautions. With a little malicious knowledge, the user could change the query and make it delete the entire table. I will not go into how this is achieved, as I’m sure there are many places that go into detail about this (Google it perhaps?).

There are many helpful little functions that PHP provides that will help you get rid of these malicious characters. I have made myself a little combination of them that you can use whenever user input needs validating. Whether it be for dynamic URLs or for form input, this will hopefully make your script more secure. There may be some unnecessary things being removed here, and yes, I have done a few things twice, but hey, it’s better to be “too” safe . Anyway, here it is…

<?php
function KeepSecure ($unsecure_thing)
{
  $result = mysql_real_escape_string(
    stripslashes(
    strip_tags(
    htmlspecialchars($unsecure_thing,
    ENT_QUOTES))));
 
  return $result;
}
?>

If you haven’t used functions before, the above script may look a little alien to you. Firstly, you should start learning because they are the most useful thing in the world. Ok, so this is how to use it.

1) The code needs to be placed near the top of your script (just after “< ?php” is preferable). Just so you declare it, before you call it. Basically, you need to tell PHP the instructions for the function, before you try and use it. A little bit of common sense there. Ideally you would have a separate file for functions/classes, and then include() it (described later).

2) Use the function like this.

<?php
$result = KeepSecure( $_GET[‘unsecure_thing’] );
?>

3) I will show you an example of how to use this script, by securing the code shown to you at the start. Instead of placing the user’s “id” input straight into the SQL query, I will pass it to the function to do it’s work on it. Then it will automatically give me it back. Then I can put it into the SQL query. So here is the new code.

<?php
 
//Connection to the database present.
//———————————————
//Declare the function
function KeepSecure ($unsecure_thing)
{
$result = mysql_real_escape_string(
stripslashes(
strip_tags(
htmlspecialchars(
 
$unsecure_thing,
 
ENT_QUOTES))));
 
return $result;
}
 
/*Get the “id” variable from the URL submitted
by user, but pass it through the function first.*/
$id = KeepSecure( $_GET[‘id’] );
 
/*Select the record from the database where the
id matches the user’s desired choice.*/
$query = “SELECT *
FROM content
WHERE id = ‘$id’”;
 
//Query the database.
$result = mysql_query($query);
 
//———————————————
//Display the page.
?>

As you can see, the information is first passed through the function, and saved into the new variable. I advise that you use this at all times to prevent SQL injections.