I Need Help With Code To Prevent SQL Injections!

[designer76]

I am having problems with a search feature I am using for a website I am building. Everything was working fine when I was testing on my local machine using EasyPHP 3.0. The issue I am having is that once I uploaded the site to a "live" server and tested it, my search function wouldn't work. The issue resides in the two lines with the magic quotes and the real escape string, for some reason those lines worked fine while testing using EasyPHP 3.0, but now I must delete those lines in order for my search function to work. The problem is that deleting those lines makes me vulnerable to an SQL injection. I have tried deleting just the magic quotes line and everything works properly, but then I am not seeing any kind of strip slashing/sanitizing when I enter in a statement like this into my search: a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't.

 

Any help on this issue would be greatly appreciated!

 

 
//get data
$button = $_GET['submit'];
if (get_magic_quotes_gpc() == 0){  
$search = mysql_real_escape_string($_GET['search']); // clean up the search string
}
else
{
$search = $_GET['search'];

$limit = 9;

$page = $_GET['page'];

if($page) 
$start = ($page - 1) * $limit;

I also tried using the mysql_real_escape_string on my construct, but I get syntax errors because of the | being used before and after the $search_each. That | character must remain in place in order for my search to work the way I want it to.

 

    
$x++;
if ($x==1)
$construct .= "keywords LIKE '%".mysql_real_escape_string(|$search_each|)."%'";
else 
$construct .= "AND keywords LIKE '%|$search_each|%'";        
}

[PFMaBiSmAd]

If get_magic_quotes_gpc() is true, you need to use stripslashes() on the data, then use mysql_real_escape_string() on the data.

 

Doing so will remove the escaping that magic_quotes_gpc is doing, then escaping the data correctly using mysql_real_escape_string().

 

Also, mysql_real_escape_string() is only helpful for string data (i.e. data that is put into a query in between single-quotes. It does nothing for numerical data (i.e. data that is NOT put into a query in between single-quotes.) For numerical data, you must either validate it as a number or cast it as a number in order to prevent sql injection.

[AbraCadaver]

As PFMaBiSmAd, it would look like this:

 

if (get_magic_quotes_gpc()){  
$search = stripslashes($_GET['search']);
} else {
$search = $_GET['search'];
}
$search = mysql_real_escape_string($_GET['search']); // clean up the search string

 

Or something like this may be easier:

 

if (get_magic_quotes_gpc()){
array_walk($_GET, 'stripslashes');
}
$search = mysql_real_escape_string($_GET['search']); // clean up the search string
// more assignments

[designer76]

Thanks for your help PFMaBiSmAd and AbraCadaver. After using your code AbraCadaver, my other problem pops up. For some reason, now my code is not reading that the search button was clicked, so no query is being run. I am getting the message that I included in my code below for people who navigate directly to my search page instead of entering a query and clicking the search button. Any thoughts as to why this may be happening? Thanks again for all of your help!

 

 

//get data
$button = $_GET['submit'];

if (get_magic_quotes_gpc()){  
        $search = stripslashes($_GET['search']);
} else {
        $search = $_GET['search'];
}
$search = mysql_real_escape_string($_GET['search']); // clean up the search string

$limit = 6;

$page = $_GET['page'];

if($page) 
$start = ($page - 1) * $limit;

// If no page var is given, set start to 0
else
$start = 0;

if (!$search)
{
$search_header = "CLICK THE SEARCH BUTTON! <span class='text'>$search</span>";
}
else
{
        $search_header = "RESULTS: <span class='text'>$search</span>";

[PFMaBiSmAd]

Unfortunately, that code has a logic error in it (it's setting $search, then setting $search again from the original $_GET value.) It also sounds like you don't have a mysql connection at that point in your code?

 

That code should be doing the following and you must have a mysql connection at the time you use mysql_real_escape_string() -

 

if(get_magic_quotes_gpc()){  
        $_GET['search'] = stripslashes($_GET['search']);
}
$search = mysql_real_escape_string($_GET['search']);

[gizmola]

The best defense against sql injection is to use mysqli, and use mysqli->prepare() and mysqli->bind_param(). 

[designer76]

Unfortunately, that code has a logic error in it (it's setting $search, then setting $search again from the original $_GET value.) It also sounds like you don't have a mysql connection at that point in your code?

 

That code should be doing the following and you must have a mysql connection at the time you use mysql_real_escape_string() -

 

if(get_magic_quotes_gpc()){  
        $_GET['search'] = stripslashes($_GET['search']);
}
$search = mysql_real_escape_string($_GET['search']);

PERFECT! That worked PFMaBiSmAd, you were right in that I was opening my database connection after that piece of code was running, and that's why it wasn't working. I just did a test and it is strip slashing just like it should be. Thanks!

[designer76]

Actually, I have one more question. How would I use this same concept to protect myself when I am collecting e-mails from people? I have a part on my site where people can enter their e-mail address into a field and then click submit and their address is stored in my database. Here is some of the example code:

 

$normal = "^[a-z0-9_\+-]+(\.[a-z0-9_\+-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*\.([a-z]{2,4})$";
$subscribe_email = $_POST['post_email'];

if (!eregi($normal, $subscribe_email)) {
$_SESSION['index_email_error'] = "Bad address!";
$errors = TRUE;
}

if ($errors == TRUE) {
header("Location: http://localhost");
die();
}

else {
$errors = FALSE;
}

$con = mysql_connect("localhost", $username, $password); //Replace with your actual MySQL DB Username and Password
mysql_select_db($database, $con); //Replace with your MySQL DB Name

if (!$con)
{
$connect_error = ($_SESSION['error'] = "Try later!" . ini_set());
header("Location: http://localhost/");
die();
}

$email = mysql_real_escape_string($_POST['post_email']); //This value has to be the same as in the HTML form file
$sql="INSERT INTO mail (mail) VALUES ('$mail')"; /*form_data is the name of the MySQL table where the form data will be saved.
name and email are the respective table fields*/

[designer76]

Is there anybody else that can chime in and assist me with my question in the post above this one? Thanks!

[AbraCadaver]

Is there anybody else that can chime in and assist me with my question in the post above this one? Thanks!

 

Just do exactly what you did with $search.

[designer76]

Is there anybody else that can chime in and assist me with my question in the post above this one? Thanks!

 

Just do exactly what you did with $search.

I did that, but when I post something similar to an SGL injection to my database, there are no slashes in the "fake injection". The text reads exactly how I typed it out.

[designer76]

Can anyone help me? I have changed my code to look like the code below but it still isn't working. I basically used the method that worked for my search feature but switched the $_GET to $_POST, but no luck. I can still post potentially harmful code to my database with no strip slashes being applied to it. Can someone lend a hand? Thanks!

 

$normal = "^[a-z0-9_\+-]+(\.[a-z0-9_\+-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*\.([a-z]{2,4})$";
$subscribe_email = $_POST['post_email'];

if (!eregi($normal, $subscribe_email)) {
$_SESSION['email_error'] = "Bad address!";
$errors = TRUE;
}

if ($errors == TRUE) {
header("Location: http://localhost");
die();
}

else {
$errors = FALSE;
}

$con = mysql_connect("localhost", $username, $password); //Replace with your actual MySQL DB Username and Password
mysql_select_db($database, $con); //Replace with your MySQL DB Name

if (!$con)
{
$connect_error = ($_SESSION['error'] = "Try later!" . ini_set());
header("Location: http://localhost/");
die();
}

if(get_magic_quotes_gpc()){  
$_POST['post_email'] = stripslashes($_POST['post_email']);
}
$mail = mysql_real_escape_string($_POST['post_email']); //This value has to be the same as in the HTML form file

$sql="INSERT INTO mail (mail) VALUES ('$mail')"; /*form_data is the name of the MySQL table where the form data will be saved.
name and email are the respective table fields*/