trying to sanitize mysql query

[jayteepics]

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]

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

[jayteepics]

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]

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]

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

 

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

[jayteepics]

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]

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

echo mysql_real_escape_string("Robert's");

[jayteepics]

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]

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]

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]

Post the revised code . . .

[jayteepics]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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