Seeking universal sanatize functon

Karebac · July 4, 2007

I am a beginner. I worked much of yesterday to write the following code which simply searches a customer table on the field of choice, looking for all records containing a certain string.

I really want to understand how to sanitize a user input field to prevent sql injection.

The problem is that when I use the various functions that I have found in google and phpfreak searches, then the query does not work properly. Specifically, if you check CUSTOMER and search on World, you should find the two rows in the table that have the word "World" in the company name.

The only thing I have found so far that works ok is the ereg_replace function which strips out anything which would NOT be expected in the input field.

I am hoping to find some function which I can use everywhere, in every circumstance, which will guard against security risks, and yet return a variable which will function properly in a sql statement.

I am a beginner, and I am not likely to become some assembly language compiler-writing guru who dreams in binary and hex, so I really depend upon blind box functions to do for me what I cannot do for myself.

And if you point me to some wonderful feature of, say PEAR, then that won't help, because I cant use PEAR. And if you point to some wonderful OOP solution, then that will not help, because I cannot seem to become fluent in Object Oriented Programming. So, I am looking for simple ways to manage data safely in PHP and MySQL.

My goal is to have a function to which I can pass $string, and have $string returned to me totally safe for use in any SQL statement.

Thanks for your help and advice!

<html>
<body>

<h3>Customers</h3>

<?

function cleanForSql($string, $allowHtml = false) {
  $string = trim($string);
  if (!$allowHtml) $string = strip_tags($string);
  $string = mysql_real_escape_string($string);
  return $string;
}

function myEscape($string) {
      // if magic quotes are on strip the slashes so we can use the proper mysql escapage.
      $string = get_magic_quotes_gpc()?stripslashes($string):$string; // since there is a difference between addslashes and mysql_real_esacpe_string
      return mysql_real_escape_string($string); // escape data properly.
}

// only validate form when form is submitted
if(isset($_POST["submit_button"])){

$string = trim($_POST["cust_input"]);
//$string = mysql_real_escape_string($string);
//$string = quote_smart($string);
//$string = sql_quote($string);
//$string = mysql_real_escape_string($string);
$string = ereg_replace("[^A-Za-z0-9]", "", $string );
//$string = myEscape($string);
//$string = cleanForSql($string);

$fieldtosearch = trim($_POST["fieldtosearch"]);

$db_host="mysql";
$db_user="YoursTruly";
$db_pass="YeahRight!";
$db="mydatabase";

mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db);

//$sql = "SELECT * FROM customer WHERE company LIKE '%$string%' order by company";

switch ($fieldtosearch){
case "pk":
                $sql = "SELECT * FROM customer WHERE pk = '$string' ";
	break;	

case "company":
                $sql = "SELECT * FROM customer WHERE company LIKE '%$string%' order by company";
	break;	

case "phone":
                $sql = "SELECT * FROM customer WHERE LEFT(phone,3) = '$string' order by company";

	break;	

case "fax":
                $sql = "SELECT * FROM customer WHERE fax LIKE '%$string%' order by company";
	break;	

case "contact":
                $sql = "SELECT * FROM customer WHERE contact LIKE '%$string%' order by company";
	break; 	
case "street":
                $sql = "SELECT * FROM customer WHERE street LIKE '%$string%' order by company";
	break; 	

case "city":
                $sql = "SELECT * FROM customer WHERE city LIKE '%$string%' order by company";
	break; 	

case "state":
                $sql = "SELECT * FROM customer WHERE state LIKE '%$string%' order by company";
        	break; 	

case "zip":
                $sql = "SELECT * FROM customer WHERE zip LIKE '%$string%' order by company";
	break; 	

case "email":
                $sql = "SELECT * FROM customer WHERE email LIKE '%$string%' order by company";
	break; 	


}



$query = mysql_query($sql) or die(mysql_error());
//$row_sql = mysql_fetch_assoc($query);
$total = mysql_num_rows($query);


while ($row_sql = mysql_fetch_array($query)) {
echo $row_sql['pk'].'-'.$row_sql['company'].'  '.$row_sql['city']. '  '. $row_sql['state']. '  '. $row_sql['zip']. '  '. $row_sql['phone'] . '  '. $row_sql['fax'] . '  '. $row_sql['contact'] . '  '. $row_sql['email'] . '<br />';



}

}
?>

<form method="POST" action="searchcustomer4.php">
<table border="1" cellpadding="7" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
    <input type="text" name="cust_input" size="20" value="<? echo $cust_input; ?>">

<p>
<input type="radio" name="fieldtosearch" value="pk"> pk Key<br>
<input type="radio" name="fieldtosearch" value="company" checked> company<br>
<input type="radio" name="fieldtosearch" value="phone"> phone<br>
<input type="radio" name="fieldtosearch" value="fax"> fax<br>
<input type="radio" name="fieldtosearch" value="contact"> contact<br>
<input type="radio" name="fieldtosearch" value="street"> street<br>
<input type="radio" name="fieldtosearch" value="city"> city<br>
<input type="radio" name="fieldtosearch" value="state"> state<br>
<input type="radio" name="fieldtosearch" value="zip"> zip<br>
<input type="radio" name="fieldtosearch" value="email"> email<br>


<p>

    <input type="submit" value="   Search   " name="submit_button"></td>


</form>

</body>
</html>

per1os · July 4, 2007

<?php
function real_escape($string) {
       return  get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes($string)):mysql_real_escape_string($string);
}
?>

Thats my variation of it.

Karebac · July 5, 2007

I think I made some progress on my problem.

Someone in IRC #php quakenet mentioned something about how there HAS to be a live connection to mysql for the command to work. I thought it was only necessary to pass $string to a function, to have it sanitized.

So, I found this link which made things clearer

http://php.net/manual/en/function.mysql-real-escape-string.php


<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));
?>

Anyway, I modified the above code and put it in my script, and now the query works ok.

Also, I wrote this little program which shows you what any sort of sanitizing function is actually returning.

<html>
<body>

<h3>Test Sanitize Code Techniques</h3>

<?


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;
}

function testfunc($value)
{
$value .= "ed";
return $value;
}

// only validate form when form is submitted
if(isset($_POST["submit_button"])){

$string = trim($_POST["cust_input"]);

//$string = $string . "ed";
//$string = sql_quote($string);
//$string = testfunc($string);
// $string = mysql_real_escape_string($string);

// Connect
$link = mysql_connect('mysql', 'YoursTruly', 'YeahRight!')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM customer WHERE company ='%s'",
            mysql_real_escape_string($string) );

$cust_input = $query;
}

?>

<form method="POST" action="testsanitizecodes.php">
<table border="1" cellpadding="7" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
    <input type="text" name="cust_input" size="100" value="<? echo $cust_input; ?>">



<p>

    <input type="submit" value="   Search   " name="submit_button"></td>


</form>

</body>
</html>

The above program calls itself, since it is named testsanitizecodes.php

You can see in the code how I tried different kinds of functions before I found the one that works for me.

You can enter a string in the field, and then the sql query string is returned in that input field so you can study it.

The other thing I learned is that, for security, you should also be examining the input string and removing from it any characters which would not normally be expected. For example:

$string = ereg_replace("[^A-Za-z0-9]", "", $string );

teng84 · July 5, 2007

$string = ereg_replace("[^A-Za-z0-9]", "", $string ); :-\ thats great

Karebac · July 5, 2007

teng84,

I have only been using php for 2 weeks. I just happened to find that line of code in a tutorial example.

teng84 · July 5, 2007

teng84,

I have only been using php for 2 weeks. I just happened to find that line of code in a tutorial example.

and whats your concern by saying those

Karebac · July 5, 2007

My concern was that you might think that I came up with the idea myself, rather than just finding it. If I remain silent and allow you to think that, then it is a form of dishonesty on my part. Search engines and forums like phpfreaks.com are great for the way they empower beginners to make rapid progress.

You were nice enough to say something positive, so I wanted to make clear that that line of code it is not my invention, but I merely found it, and wish to give the credit to someone else. Since you were kind enough to say something positive and encouraging, I thought I would be polite and answer you in some fashion. This is simply my notion of being friendly, sociable and polite. What more can I say? Thanks for your time and interest in reading my post.

trq · July 5, 2007

I have only been using php for 2 weeks.

Can I make a small suggestion seeing as your just starting? Not to do with your script really, but just good practice in general. Get in the habbit of using the full <?php tags. Alot of servers do not support the short <? form. It can also run a much when your trying to use xml. Hence, they have been depricated.

Karebac · July 5, 2007

Thanks Thorp. I was wondering about that this morning.

Sign In

Seeking universal sanatize functon

Recommended Posts

Karebac

Link to comment

Share on other sites

per1os

Link to comment

Share on other sites

Karebac

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

Karebac

Link to comment

Share on other sites

teng84

Link to comment

Share on other sites

Karebac

Link to comment

Share on other sites

trq

Link to comment

Share on other sites

Karebac

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information