Solved

PHP SQL Injection prevention and Mysql security

Posted on 2007-12-05
21
1,169 Views
Last Modified: 2010-04-21
Hi all,

I am coming close to publishing my site and i have used PHP and MySQL for all of the site.

I want to ensure i am as secure as i can be and i fear i dont know enough about SQl injection prevention and i dont want to get stung.

My site has email forms which i understand is one of my main threats. When i was creating this form a guy on EE said you are open to SQL injection attacks but didnt say anymore.

Can anyone give me some advice on h0ow to go about securing my site? quite a broad question i know but things like:
Lock web folders that contain my database connection details(includes)
Dont echo errors on pages so the public wont see errors if there is a problem with the site.
How to turn off my sql errors - Can i echo a custom Mysql error page instead of an actual error being posted and therefore giving info away i dont want to.

I know you guys know your stuff so all advice is welcome.
0
Comment
Question by:satmanuk
  • 11
  • 5
  • 5
21 Comments
 
LVL 10

Accepted Solution

by:
wildzero earned 250 total points
ID: 20416677
I think that was me back on this question
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_22974139.html

As we can see by looking at that code. You are letting unescaped / sanatized user input go straight into your HTML and mySql queries.

See here
&nbsp;<?php if(isset($_REQUEST["street"])) echo $_REQUEST["street"]; ?>

I could then do
yourpage.php?street=<b>THIS IS BOLD</b>
or even
yourpage.php?street=somejavascripthere

so you want to make sure you are removing all 'illegal' characters and stripping off anything that shouldn't be there.

With your mysql you need to be 'escaping' all the content. And there is a php function to help with that called mysql_real_escape_string. http://nz2.php.net/mysql_real_escape_string


0
 
LVL 10

Expert Comment

by:wildzero
ID: 20416688
So to sum up, sanatize all user input - remove anything that shouldn't be there - validate there input and just plain ol don't trust it and escape everything that you stick into a mysql query.

http://www.askbee.net/articles/php/SQL_Injection/sql_injection.html

have a read of that.
0
 
LVL 1

Author Comment

by:satmanuk
ID: 20417032
indeed it was you. Well spotted.

I am unsure as to when i use what. I like noobie instructions when i am learning something. Like a example for a bad use oif code and one that deals with the SQl injection.

Is it just a case of using mysql_real_escape_string?

This is new to me. Is the general principle to mysql_real_escape_string all inputs? is that it or am i missing the point?

So apply this to all my inputs:
$value = mysql_real_escape_string( $value );
0
 
LVL 10

Expert Comment

by:wildzero
ID: 20417065
if you read up on that link it posted, it shows exmaples of good and bad :-)
will reply better in a tick
0
 
LVL 31

Assisted Solution

by:Frosty555
Frosty555 earned 250 total points
ID: 20417382
Pretty much any user-driven data that will appear in a MySQL query, you should pass through mysql_real_escape_string() first. This will prevent server-side SQL injection attacks.

Also, any user-driven data you are planning on echo'ing out to the page should be passed through htmlspecialchars() first. This will replace various html special symbols with their excaped equivalent. E.g. "<" turns into "&lt;". This prevents client-side Javascript or HTML injection attacks.

mysql_real_escape_string() uses data from the actual mysql connection to figure out what to escape and how, and so it requires that you pass in the LinkID of the connection to the database every time you use it. If it doesn't work, mysql_escape_string() is a more generic but reasonably acceptable alternative.

Now, for me, personally I really hate it when I have to remember to put single quotes around every string I ever pass to MySQL. I also hate having "NULL" in my queries, because so many things when compared to NULL always return true. You can easily zap an entire table that way ;). So I use this little chunk of code to do it for me.

Then anything I put into a SQL statement I just run through this function first.

function tosql($expr, $link_id)

{

	if( is_bool( $expr ) )

	{

		if( $expr )

			return '1';

		else

			return '0';

	}

	elseif( is_double($expr) or is_float($expr) )

	{

		return strval(doubleval($expr));

	}

	elseif( is_int($expr) or is_long($expr) )

	{

		return strval(intval($expr));

	}

	elseif( is_null($expr) )

	{

		return '\'\'';

	}

	else

	{

		return mysql_real_escape_string( $expr, $link_id );

	}

}

Open in new window

0
 
LVL 1

Author Comment

by:satmanuk
ID: 20422829
On the link wildzero provided they give this function attached.

I am relatively new to PHP functions so bare with my newbie understanding.

So i can either add the function at the top of each of my pages or i could include a function.php file with just the function code in it? yes?

Then this part that i am not fully getting is when i apply the function.

I understand this example:
$username = $_POST['username'];
query = "SELECT * FROM users WHERE username='" . sql_quote($username) . "'";

But when else would i use the function? say for example i have an email form that doesnt use mysql, i understand this is too a problem with injection issues.

Sorry to ask so many questions but as you can imagine i want to fully understand this so i dont get had by those pescy script kiddies.

Thanks guys!


function sql_quote( $value ) 

{ 

if( get_magic_quotes_gpc() ) 

{ 

      $value = stripslashes( $value ); 

} 

//check if this function exists 

if( function_exists( "mysql_real_escape_string" ) ) 

{ 

      $value = mysql_real_escape_string( $value ); 

} 

//for PHP version < 4.3.0 use addslashes 

else 

{ 

      $value = addslashes( $value ); 

} 

return $value; 

} 

Open in new window

0
 
LVL 1

Author Comment

by:satmanuk
ID: 20422852
Frosty you wrote: Also, any user-driven data you are planning on echo'ing out to the page should be passed through htmlspecialchars() first.
Could you give an example on the htmlspecialchars? this is new to me.... :)


Thanks!
0
 
LVL 10

Expert Comment

by:wildzero
ID: 20422901
hi there,

Thats correct you would stick that inside a function however Frosty looks pretty good as well so could use either really.

Your right, that code wouldn't  stop email abuse, what you need to do there is check any fields other than the body message don't have any new line characters - and that the To and/or From address only contain one email address.
0
 
LVL 1

Author Comment

by:satmanuk
ID: 20423059
Hi ya Wildzero,

On the other post you said something about cleaning the request array. Do you know what i mean when i dont actually know when to use the function.
On the example on the url you gave i uhnderstood the example given.
This is correct
$username = $_POST['username'];
query = "SELECT * FROM users WHERE username='" . sql_quote($username) . "'";
This is open to attack
$username = $_POST['username'];
query = "SELECT * FROM users WHERE username='$username';

How do i know when to sql_quote a variable?
0
 
LVL 1

Author Comment

by:satmanuk
ID: 20424505
Attached is my login script. Its a combination of the built in dreamweaver user authentication with a bit of manual editing of things.
I can see there is use of get_magic_quotes_gpc() which as i understand it is not something to rely on as although its switched on most servers its soon to be turned off. so mysql_real_escape_string is the way forward.
Now all i have to do is understand when to apply the function that apparently sorts out the problem.

How would i go about altering my login code so its secure using either of the functions mentioned above.?

Thanks in advance
<?php require_once('Connections/EE_con.php'); ?>

<?php

// *** Validate request to login to this site.

if (!isset($_SESSION)) {

  session_start();

}
 

$loginFormAction = $_SERVER['PHP_SELF'];

if (isset($_GET['accesscheck'])) {

  $_SESSION['PrevUrl'] = $_GET['accesscheck'];

}
 

if (isset($_POST['email'])) {

  $loginUsername=$_POST['email'];

  $password=$_POST['password'];

  $MM_fldUserAuthorization = "user_level";

  $MM_redirectLoginSuccess = "Admin.php";

  $MM_redirectLoginFailed = "sr_login.php?message=The email or password you entered is incorrect, please try again!";

  $MM_redirecttoReferrer = true;

  mysql_select_db($database_EE_con, $EE_con);

  	

$LoginRS__query=sprintf("SELECT user_email, user_password, user_level FROM users WHERE user_email='%s' AND user_password=SHA1('%s')",

     get_magic_quotes_gpc() ? $loginUsername : addslashes($loginUsername), get_magic_quotes_gpc() ? $password : addslashes($password));

   

  $LoginRS = mysql_query($LoginRS__query, $EE_con) or die(mysql_error());

  $loginFoundUser = mysql_num_rows($LoginRS);

  if ($loginFoundUser) {

    

    $loginStrGroup  = mysql_result($LoginRS,0,'user_level');

    

    //declare two session variables and assign them

    $_SESSION['MM_Username'] = $loginUsername;

    $_SESSION['MM_UserGroup'] = $loginStrGroup;	      
 

    if (isset($_SESSION['PrevUrl']) && true) {

      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	

    }

    header("Location: " . $MM_redirectLoginSuccess );

  }

  else {

    header("Location: ". $MM_redirectLoginFailed );

  }

}

?>

Open in new window

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 10

Expert Comment

by:wildzero
ID: 20424543
Give this a whirl, I modifed some of the other code as well, and re-decleared $loginUsername (see code)
<?php require_once('Connections/EE_con.php'); ?>

<?php

// *** Validate request to login to this site.

if (!isset($_SESSION)) {

  session_start();

}

 

$loginFormAction = $_SERVER['PHP_SELF'];

if (isset($_GET['accesscheck'])) {

  $_SESSION['PrevUrl'] = $_GET['accesscheck'];

}

 

if (isset($_POST['email'])) {

  $loginUsername  = $_POST['email'];

  $password       = $_POST['password'];

  

  $MM_fldUserAuthorization = "user_level";

  $MM_redirectLoginSuccess = "Admin.php";

  $MM_redirectLoginFailed = "sr_login.php?message=The email or password you entered is incorrect, please try again!";

  $MM_redirecttoReferrer = true;

  

  mysql_select_db($database_EE_con, $EE_con);

  	

  $LoginRS__query=sprintf("SELECT user_email, user_password, user_level FROM users WHERE user_email='%s' AND user_password=SHA1('%s')",

     sql_quote($loginUsername), sql_quote($password));

   

  $LoginRS = mysql_query($LoginRS__query, $EE_con) or die(mysql_error());

  If (mysql_num_rows($LoginRS) > 0) {

    $LoginRS = mysql_fetch_assoc($LoginRS);
 

    

    $loginStrGroup  = $LoginRS['user_level'];

    // -- we reset this variable with data from the DB, don't want to pollute the session below

    $loginUsername  = $LoginRS['user_email'];  

    

    

    //declare two session variables and assign them

    $_SESSION['MM_Username'] = $loginUsername;

    $_SESSION['MM_UserGroup'] = $loginStrGroup;	 

         

 

    if (isset($_SESSION['PrevUrl']) && true) {

      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	

    }

    header("Location: " . $MM_redirectLoginSuccess );

    Exit;

  }

  else {

    header("Location: ". $MM_redirectLoginFailed );

    Exit;

  }

}

?>
 

######

## Put this in Connections/EE_con.php

######

function sql_quote( $value ) 

{ 

  if( get_magic_quotes_gpc() ) { 

    $value = stripslashes( $value ); 

  } 

  

  //check if this function exists 

  if( function_exists( "mysql_real_escape_string" ) ) { 

    $value = mysql_real_escape_string( $value ); 

  } else { 

     $value = addslashes( $value ); 

  } 

  return $value; 

} 

Open in new window

0
 
LVL 1

Author Comment

by:satmanuk
ID: 20424598
Ah thanks! i have added your code and put the function in my connection script.

All works the same. I think the main problem for me is i dont understand how the SQl injection works i.e how would i know my code is secure even now? i can see what changes you made but dont follow the logic as to when to use it, i will end up wrapping sql_quote round everything through paranoya

Also on my login page i echo a message: <?php if(isset($_REQUEST["message"])) echo $_REQUEST["message"]; ?>

How would i clean this?


Thanks loads for the help! much appreciated.
0
 
LVL 31

Expert Comment

by:Frosty555
ID: 20424840
=== SQL INJECTION ATTACKS ===

Here, let me give you some backgrounder on various kinds of injection attacks, which is what you're trying to protect yourself form here.

Normally, you send a query to the sql server. You might, say, submit a query to insert a new record. E.g.:

         INSERT INTO log (name, comment) VALUES ('john doe', 'hello! this is me');

The name "john doe" and the comment "hello! this is me" would be provided by the user, via a form on another page. So your user would have typed in that data. Now, what stops the user from putting this exact text into his comment?

       '); DELETE FROM log WHERE *; //

Notice, if you were to plug this text into the query you made, you'd get this:

         INSERT INTO log (name, comment) VALUES ('john doe', ''); DELETE FROM log WHERE *; //');

You see, the comment effectively closed the string, finished up the sql query, and executed its own query! A user could destroy your entire database. Now, if you actually WANTED a single quote in a string in mysql, you would escape it with a backslash. E.g. if your user wanted to say "that's cool!" as his comment (note the ' there), you would instead put "that\'s cool!". MySQL knows that you didn't intend that quote to be the end of the string.

        INSERT INTO log (name, comment) VALUES ('john doe', 'that\'s cool!);

But you need to know to actually do that, and you need to go into every string the user gives you, and properly escape all the special characters in his string. There's a variety of other clever ways for a user to "inject" code into your sql statement this way. This is why the mysql_real_escape_string() function exists. If you give it the string "that's cool!" it will return "that\'s cool!". It effectively backslashes a string making it impossible for the user to get "outside" of the string and into your sql code. The string it returns is safe to put put into a sql query.

The moral of the story is any string in your query that was specified by the user should be properly "escaped" as to prevent injected code from being executed.


=== HTML INJECTION ATTACKS ===

Similarely, lets say your user posted a comment on a little blog you wrote in php. You would have a page somewhere that displayed his comment, e.g:

<table>
  <tr><td>Name</td><td>John Doe</td></tr>
  <tr><td>Comment</td><td>Hello! My name is John!</td></tr>
</table>

So, what stops the user from putting this text into his comment:

     Hello! My name is John<script>alert("bwahaha I'm a hacker!");</script>

Your page would happily take this text and drop it into your page, resulting in:

<table>
  <tr><td>Name</td><td>John Doe</td></tr>
  <tr><td>Comment</td><td>Hello! My name is John<script>alert("bwahaha I'm a hacker!");</script></td></tr>
</table>

And whenever a user visited your comment page, it would pop up a javascript alert. He injected code into your page. This can be obnoxious, or break the appearance of your webpage. Script kiddies get a kick out of it.

Again, in HTML, all the special characters can be escaped. For example, a < can  be rewritten as "&lt;". If you actually WANTED to put the text "<script>" out onto the page, you would actually put the html "&lt;script&gt;" out. The htmlspecialchars() function does exactly that.

If you pass "<script>" to the htmlspecialchars() function, it will return "&lt;script&gt;". This string is something safe to be echo'd out to your page, without fear of html injection.


======================

So you can see how to use these functions. You take the user's raw input and escape it with the appropriate function to ensure it is safe to be used in your page.

Some people prefer to simply take the *entire* $_REQUEST[] array in php (which, by the way, stores all the data in the form that the user was typing in the page before), and simply run all of that through the escaping functions. This is a good way to ensure you didn't miss anything stupid. Personally I think it gives you a false sense of security so I don't do it that way. It is up to your own discretion if you want to do this, or simply remember to escape everything in every queries.
0
 
LVL 31

Expert Comment

by:Frosty555
ID: 20424859
And for your above question, if you wanted to echo out the "Message" entry, like you said:
    <?php
         if( isset($_REQUEST["message"]) )
               echo $_REQUEST["message"];
    ?>

What you'd do, is you would put that data through htmlspecialchars() first. e.g:

    <?php
         if( isset($_REQUEST["message"]) )
               echo htmlspecialchars($_REQUEST["message"]);
    ?>

0
 
LVL 1

Author Comment

by:satmanuk
ID: 20431183
excellent explanation. i am getting it now.

With the  
if( isset($_REQUEST["message"]) )
               echo htmlspecialchars($_REQUEST["message"]);
I can easily test this and can see it working. How do i test that the mysql_real_escape_string is working?
Like for example how would i see that this is open to attack:
$username = $_POST['username'];
query = "SELECT * FROM users WHERE username='$username';

Thanks
0
 
LVL 1

Author Comment

by:satmanuk
ID: 20431304
Basically what could i put in the $_POST['username'] field on my html form that will show me something from my database that i wouldnt want a user to get to.
0
 
LVL 31

Expert Comment

by:Frosty555
ID: 20432042
Well, for this you need to be a little clever. Basically, scattering single quotes and various foreign characters or null characters tend to cause mysql errors.

The best way to test is to sprinkle some single quotes in, and then echo out the $query and see if they got properly escaped. If they did you know its working.
0
 
LVL 1

Author Comment

by:satmanuk
ID: 20432440
I found this in my registration script which i built in dreamweaver. I take it this is Dreamweavers function that deals with injection?
i did what you said on my registration page and when i added single quotes it returned '\'\'\'\''\'\ so somethign is taking care of it.

If i am right the code snippet i have attached is the function dreamweaver puts in to deal with the isse of injection but if i am right, this uses magic quotes which isnt a long term protection. so i guess i should remove that function and use the one from here to be safe.

thanks loads Frosty you have helped me understand this.

I appreciate your help.
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 

{

  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
 

  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;    

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

Open in new window

0
 
LVL 31

Expert Comment

by:Frosty555
ID: 20432889
See, on some servers when you submit data, it is automatically backslashed by php (to prevent users who aren't in the know from making vunerable programs). It of course wouldn't be good for you to "double" escape your text :P, so this function handles that.

The addslashes() function is php's generic equivalent to mysql_real_escape_string(), meant for use with a variety of different database systems. I think historically the addslashes() funciton was introduced first into php. Some servers that don't have mysql need a method of backslashing too. addslashes() is really just more compatible, that's why dreamweaver uses it.

It is probably better to change that to mysql_real_escape_string(), but it is definately handling your basic insert-a-single-quote method of injection attack. I think the only danger is maybe addslashes() will accidentally backslash something that mysql doesn't support backslashing on.
0
 
LVL 1

Author Comment

by:satmanuk
ID: 20434584
ok i see, cheers Frosty.  
0
 
LVL 1

Author Closing Comment

by:satmanuk
ID: 31413031
I started off clueless to the subject and now i totally get it.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now