trying to sanitize mysql query

jayteepics · September 7, 2011

Folks this one line and any other variations I have tried just nulls my variable

function check_input($value)
{
echo '<pre>';
echo "Value before = ";
echo $value;
echo '</pre>';
// Stripslashes
//if (get_magic_quotes_gpc())
//   {
//   $value = stripslashes($value);
//   }
// Quote if not a number
//if (!is_numeric($value))
//   {
   $value = "'" . mysql_real_escape_string($value) . "'";   <-----
//$value = mysql_real_escape_string($value);
//$value = mysql_real_escape_string($value);
echo '<pre>';
echo "Value after = ";
echo $value;
echo '</pre>';
//   }
return $value;
}

...
// Make a safe SQL
$iso_code = check_input($iso_code);
$country_name = check_input($country_name);
      $query = "select * from countries where
            iso_code = '".$iso_code."' or country like '%".$country_name."%'";
      mysql_query($query);
echo '<pre>';
echo $iso_code;
echo $country_name;
echo $query;
echo '</pre>';

The result is:-

Value before = UK
Value after = ''
Value before = United Kingdom
Value after = ''
''''select * from countries where
            iso_code = '''' or country like '%''%'

with no mysql_real_escape statement the app work fine. I'm now trying to make my code more robust.

Any help would be appreciated.

jamie

jayteepics · September 7, 2011

FWIW I should have stated that magic quotes is OFF in php.ini

jayteepics · September 7, 2011

Hmmn,

It works with..

mysql_real_escape_string($value);

Duh!

Serves me right for slavishly assuming proffered functions work..

Unless this is still wrong anyone?

Jamie

jayteepics · September 7, 2011

I am using this sample from w3schools

<?php
function check_input($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
   {
   $value = stripslashes($value);
   }
// Quote if not a number
if (!is_numeric($value))
   {
   $value = "'" . mysql_real_escape_string($value) . "'";
   }
return $value;
}

$con = mysql_connect("localhost", "peter", "abc123");
if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

// Make a safe SQL
$user = check_input($_POST['user']);
$pwd = check_input($_POST['pwd']);
$sql = "SELECT * FROM users WHERE
user=$user AND password=$pwd";

mysql_query($sql);

mysql_close($con);
?>

and they are assigning the result back into $value, so my change to..

mysql_real_escape_string($value); is probably really stupid as it isn't actually doing anything hence it works as before BUT isn't sanitizing anything is it?

Jamie

Pikachu2000 · September 7, 2011

Try it with a value that contains characters that would be escaped and see.

echo check_input("robert's"); // should return robert\'s

jayteepics · September 7, 2011

Thanks I tried that and it is still null

I put this statement back in...

$value = mysql_real_escape_string($value);

result...

Value before = robert's
Value after = 
Value before = FR
Value after = 
Value before = France
Value after =

Pikachu2000 · September 7, 2011

Get rid of the function altogether for the moment, and see what this outputs.

echo mysql_real_escape_string("Robert's");

jayteepics · September 7, 2011

Interesting, I did that and it returned null...

Robert String <------ result should be here

FRFranceselect * from countries where

iso_code = 'FR' or country like '%France%'

Num Rows 2Region EuropeShip_cost 10.00

Country Code (ISO standard):

Country Name:

Country ISO Code = FR

Country Name = France

Country Region = Europe

Country Ship Cost = £10.00

Avoided the function the app works as before

putting your suggested echo in gave null result

jayteepics · September 7, 2011

Has anyone else experienced the phenomenon where mysql_real_escape_string() wipes out the contents of the variable?

Code compiled using NetBeans 6.9.1

I've checked for dependencies on my local Windows 7 / Apache 2 httpd.conf / PHP 5.x installation php.ini also shipped the code to the ISP Linux Apache / PHP/ MySQL platform and get the same results?

Does anyone have any ideas or do I just change the sanitization by using other methods?

Jamie

jayteepics · September 7, 2011

If I can successfully issue a MySQL query...

$query = "select * from countries where
          iso_code = '".$iso_code."' or country like '%".$country_name."%'";

mysql_query($query);

does it follow that I have the appropriate libs available to be able to successfully execute a...

$value = mysql_real_escape_string($value);

All I can imagine is there must be something unavailable else why would $value get set to '' ?

Any help appreciated

Jamie

Pikachu2000 · September 8, 2011

Post the revised code . . .

jayteepics · September 8, 2011

Many Thanks for taking another look much appreciated.

Here is the code...

$iso_code = trim($_POST['iso_code']);
$iso_code = strtoupper($iso_code);
$country_name = trim($_POST['country_name']);
$country_name = ucwords($country_name);
///------------Do Validations-------------
if(empty($iso_code) && empty($country_name))
{
	$errors .= "\n Either ISO Code or Country Name is required. ";
}

if(empty($errors))
{
	//send the form

// Make a safe SQL
//            echo check_input("robert's"); // should return robert\'s
//   echo '<pre>';
//   echo "Robert String = ";
//   echo mysql_real_escape_string("Robert's");
//   echo "should have seen result";
//   echo '</pre>';

$iso_code         = check_input($iso_code);
$country_name = check_input($country_name);

  $query = "select * from countries where
            iso_code = '".$iso_code."' or country like '%".$country_name."%'";

  mysql_query($query);

Here is the pared down function...

function check_input($value) {
echo '<pre>';
echo "Value before = ";
echo $value;
echo '</pre>';
// Stripslashes
//if (get_magic_quotes_gpc())
//   {
//   $value = stripslashes($value);
//   }
// Quote if not a number
//if (!is_numeric($value))
//   {
//   $value = "'" . mysql_real_escape_string($value) . "'";
//   $value = mysqli_real_escape_string($value);
      $value = mysql_real_escape_string($value);
//
//     $value = addslashes($value);
echo '<pre>';
echo "Value after = ";
echo $value;
echo '</pre>';
//   }
return $value;
}

Here is the result...

Check Country for Shipping

Value before = US
Value after = 
Value before = United States
Value after = 
Num Rows 249Country AndorraRegion EuropeShip_cost 10.00

Country Code (ISO standard): 


Country Name: 


Country ISO Code   = AD

Country Name          = Andorra

Country Region        = Europe

Country Ship Cost  = £10.00


Return Home

By toggling from real_escape_string() in the function to addslashes() only change..

the result is...

Check Country for Shipping

Value before = UK
Value after = UK
Value before = United Kingdom
Value after = United Kingdom
Num Rows 1Country United KingdomRegion EuropeShip_cost 10.00

Country Code (ISO standard): 


Country Name: 


Country ISO Code   = UK

Country Name          = United Kingdom

Country Region        = Europe

Country Ship Cost  = £10.00


Return Home

As I mentioned before, I don't need to use the function a simple

$value = mysql_real_escape_string($value);

wipes out $value

jayteepics · September 8, 2011

A Question:

Has this actually got everyone stumped?

Or is it so stupid it just might not be worth answering?

I realise I'm a newbie but how can one single statement just not work for me but works for everyone else.

Is it wrong?

Is it possibly an environment issue (I have run it on two different environments) but php.ini is kind of similar apart from one being Windows and the other being linux?

Again, any help would be appreciated.

Jamie

dougjohnson · September 8, 2011

You are "Connecting" to a database right? You can't use mysql_real_escape_string without first connecting to a db. Sorry if missed something but I didn't see a connection.

Pikachu2000 · September 8, 2011

Are you developing with display_errors = On and error_reporting = -1 in your php.ini file? If not, you should be.

Make sure you're connected to the database before mysql_real_escape_string() is called.

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

xyph · September 8, 2011

You are "Connecting" to a database right? You can't use mysql_real_escape_string without first connecting to a db. Sorry if missed something but I didn't see a connection.

^^^^^^ THIS ^^^^^^^

The following code:

<?php

$var = 'Something';

echo 'BEFORE:<br>';
echo check_input($var) . '<br><br><br>AFTER:';

mysql_connect('localhost','root','');

echo check_input($var);

function check_input($value) {
echo '<pre>';
echo "Value before = ";
echo $value;
echo '</pre>';
// Stripslashes
//if (get_magic_quotes_gpc())
//   {
//   $value = stripslashes($value);
//   }
// Quote if not a number
//if (!is_numeric($value))
//   {
//   $value = "'" . mysql_real_escape_string($value) . "'";
//   $value = mysqli_real_escape_string($value);
      $value = mysql_real_escape_string($value);
//
//     $value = addslashes($value);
echo '<pre>';
echo "Value after = ";
echo $value;
echo '</pre>';
//   }
return $value;
}

?>

Output:

BEFORE:
Value before = Something

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'SYSTEM'@'localhost' (using password: NO) in C:\Apps\wamp\www\examples\temp.php on line 27

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\Apps\wamp\www\examples\temp.php on line 27
Value after = 



AFTER:
Value before = Something
Value after = Something
Something

Perhaps you should turn on error_reporting and display_errors

jayteepics · September 9, 2011

Answers:

Yes I'm connected I've showed this in the code below, the select query always works so have to be connected

display_errors is On and error_reporting is E_ALL

Full code no function calls..

<?php
include ('book_sc_fns.php');
$conn = db_connect();
if (!$conn) {
    echo "Failed to connect to Database";
    exit;
}
session_start();
$errors = '';
$iso_code = '';
$country_name = '';

do_html_heading("Check Country for Shipping");
$iso_code = trim($_POST['iso_code']);
$iso_code = strtoupper($iso_code);
$country_name = trim($_POST['country_name']);
$country_name = ucwords($country_name);
///------------Do Validations-------------
if (empty($iso_code) && empty($country_name)) {
    $errors .= "\n Either ISO Code or Country Name is required. ";
}
if (empty($errors)) {
   echo '<pre>';
   print "Robert String = \n";
   print mysql_real_escape_string("Robert's");
   print "should have seen result Robert\'s \n";
   print "ISO before = \n";
   print $iso_code."\n";
   print "Country before = \n";
   print $country_name;
   echo '</pre>';
   //$iso_code = mysql_real_escape_string($iso_code);           //  uncommenting these
   //$country_name = mysql_real_escape_string($country_name);   //  wipes out the variable
    $country_name = addslashes($country_name);
    if (!$country_name)
        $country_name = "blank";
    $query = "select * from countries where
          iso_code = '" . $iso_code . "' or country like '%" . $country_name . "%'";

    mysql_query($query);
echo '<pre>';
   print "ISO after = \n";
   print $iso_code."\n";
   print "Country after = \n";
   print $country_name;
echo '</pre>';
    if (mysql_errno($conn)) {
        echo 'Error: Could not find country.  Please try again.';
        echo mysqli_errno($conn);
        echo mysqli_error($conn);
        exit;
    }
    $result = $conn->query($query);
    $num_results = $result->num_rows;
}

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <title>Shipping Information</title>
        <!-- define some style elements-->
        <style>
            label,a, body
            {
                font-family : Arial, Helvetica, sans-serif;
                font-size : 12px;
            }
            .err
            {
                font-family : Verdana, Helvetica, sans-serif;
                font-size : 12px;
                color: red;
            }
            .style1 {
                text-align: center;
            }
        </style>
    </head>

    <body style="background-color: #C9C9C9">

        <table align="center" style="width: 100%">
            <tr>
                <td class="style1">

                </td>
            </tr>
        </table>
         <?php
if (!empty($errors)) {
    echo "<p class='err'>" . nl2br($errors) . "</p>";
}
?><div id='country_check_errorloc' class='err'></div>
        <form method="POST" name="shipping_info_form"
              action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>">
            <p>
                <label for='iso_code'>Country Code (ISO standard): </label><br>
                <input type="text" name="iso_code" value="">
            </p>
            <p>
                <label for='country_name'>Country Name: </label><br>
                <input type="text" name="country_name" value="">
<?php
if ($num_results > 0) {

    for ($i = 0; $i < $num_results; $i++) {
        $row = $result->fetch_assoc();
        echo "</strong><br />    ISO Code: ";
        echo $row['iso_code'];
        echo "</strong><br />    Country: ";
        echo $row['country'];
        echo "</strong><br />    Region: ";
        echo $row['region'];
        echo "<br />    Ship Cost: &#163;";
        echo $row['ship_cost'];
        echo "</p>";
    }
}

?>

                <br><input type="submit" value="Submit" name='submit'><br />
            <p class="style5">
                <a title="return to site" target="_self" href="../default.htm">
				Return Home</a></p>
        </form>
    </body>
</html>

Result with comments..

Result with comments in...

Check Country for Shipping

Robert String = 
should have seen result Robert\'s 
ISO before = 
UK
Country before = 
United Kingdom
ISO after = 
UK
Country after = 
United Kingdom

Country Code (ISO standard): 


Country Name: 

    ISO Code: UK
    Country: United Kingdom
    Region: Europe
    Ship Cost: £10.00



Return Home

remove comments ...



Check Country for Shipping

Robert String = 
should have seen result Robert\'s 
ISO before = 
UK
Country before = 
United Kingdom
ISO after = 

Country after = 
blank

Country Code (ISO standard): 


Country Name: 


Return Home

the only error output I can find is anxdebug output called cachegrind.out which I can post if it helps?

Many Thanks

jamie

jayteepics · September 9, 2011

OK. I guess I should have run this code supplied by XYPH before..

in fact I guess I should have shown my connect function before!!

<?php
$var = 'Something';

echo 'BEFORE:<br>';
echo check_input($var) . '<br><br><br>AFTER:';

mysql_connect('localhost', 'xxxxxxx', 'yyyyyyy', 'zzzzzz');
echo check_input($var);

function check_input($value) {
echo '<pre>';
echo "Value before = ";
echo $value;
echo '</pre>';
// Stripslashes
//if (get_magic_quotes_gpc())
// {
// $value = stripslashes($value);
// }
// Quote if not a number
//if (!is_numeric($value))
// {
// $value = "'" . mysql_real_escape_string($value) . "'";
// $value = mysqli_real_escape_string($value);
$value = mysql_real_escape_string($value);
//
// $value = addslashes($value);
echo '<pre>';
echo "Value after = ";
echo $value;
echo '</pre>';
// }
return $value;
}

?>

Thanks XYPH

Using the code you supplied connecting using

mysql_connect('localhost', 'xxxxx', 'yyyyy', 'zzzzzz');

BEFORE:

Value before = Something

Value after =

AFTER:

Value before = Something

Value after = Something

Something

Worked!!!!

________________________________________

using

my $conn = db_connect(); function which contains..

function db_connect() {

$result = new mysqli('localhost', 'xxxxxxx', 'yyyyyyy', 'zzzzzzz');

if (!$result) {

return false;

}

$result->autocommit(TRUE);

return $result;

}

I get the failure !!!

BEFORE:

Value before = Something

Value after =

AFTER:

Value before = Something

Value after =

Failed!!

Drat!! I mean THANKS!!

Could someone explain why my connect method doesn't work? making the real_escape_string mysqli_real_escape_string fails too.

Note: at no time did I get any error displayed - despite the display_errors being On!

So obviously I was connected all the time else none of my stuff would have been working HOWEVER

my connection method was not compatible it seems with mysql_real_escape_string and that's the first failure which has cropped up so far.

Hmm

Many thanks for everyone looking at this and helping

Jamie

jayteepics · September 9, 2011

Further lessons....

My Apache 2.2 server has been up for weeks....

php.ini display_errors change was done about a week ago...

I have only just restarted Apache and I am seeing a shedload of errors NOW in the browser.

My abject apologies to all:-

If it is any consolation what I have learnt with this one post (though I still have to understand the connection differences) is...

Pare down the coding problem to just the heart of the problem AND if any functions are used, include them with the code so everyone can see the entire story !

AND

Don't forget to restart Apache server if any change has been made

Hopefully this hasn't p***ed anyone off.

Jamie

xyph · September 9, 2011

You have to pass your $conn object to your function and use $conn->escape_string($val)

Your MySQL connection is held entirely in your $conn object. Function like mysql_query() or mysql_real_escape_string() won't work. That's a huge benefit, or in this case a bane, of using OOP over procedural. You don't have global connections/handles flying all over the place.

Why you're using OOP in a procedural-style script is beyond me, but there's your problem.

Here's an example of what I mean

<?php 

$conn = db_connect();
$var = 'Something';

echo check_input( $var, $conn );

function db_connect() {
return new mysqli('localhost','root','','db');
}

function check_input( $value, mysqli $db ) {
echo '<pre>';
echo "Value before = ";
echo $value;
echo '</pre>';
if (get_magic_quotes_gpc()) 
	$value = stripslashes($value);
if (!is_numeric($value))
	$value = "'" . $db->escape_string($value) . "'";
echo '<pre>';
echo "Value after = ";
echo $value;
echo '</pre>';
return $value;
}

?>

jayteepics · September 11, 2011

Many Thanks XYPH for taking time to explain this. It is very much appreciated!

As a beginner, I guess we are molded by our first impressions and mine are coming from the "PHP and MySQL Web Development" book Fourth Edition by Luke Welling and Laura Thomson.

To be fair I guess they have to demonstrate a variety of ways of coding either that it was written by a committee ;-)

The mix and match of OOP and Procedural is right there in the source samples and I'm too much a newb to know any better :-(

Thanks again to all!

Jamie

Sign In

trying to sanitize mysql query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information