mysql_real_escape_string()

[russia5]

I have just put MySQL_real_escape_string() on my form variables.  Is there an input I can put in a text field that I can go to my admin and see if the code is escaping the characters ie) if the mysql_real_escape_string() is working?

[wildteen88]

use echo

[russia5]

Sorry, let me clarify the question a bit.  I have never used mysql_real_escape_string() so sorry if the question is elementary.  I am uder the impression, that it takes characters, namely ', and escapet them ie) /'  So, it seems to me, if I was to put testing'  in my text box, in my admin panel, I should get testing/'  The problem is that I am not.  (the admin panel is an output of the database)

[wildteen88]

It is doing the trick I assure, otherwise your SQL Query would fail. When you get the data out of the database mysql will unescape the previously escaped characters.

You should be able to see the escape characters when you look into your database, you can do by using phpMyAdmin which most hosts provide to help manage your mysql databases.

[russia5]

Ooohhh.... Thankyou very much!  I was having fits.  How does mysql know to do that?

[wildteen88]

I ment PHP rather than MySQL. PHP is smart enough to recognise escaped characters within a string and attempts to unescape them when they are outputted.

[russia5]

Oh Boy... it didn't work.  I went to the database and the table showed all of the characters just the way I put them in.

I will post the code in case you would be kind enough to take a look and see if you see anything.

(Thanks again in a major way!)

<?php
//the variables below are an abbreviated list
name = trim($_POST['name']);
$city = trim($_POST['city']);
$country = trim($_POST['country']);

$name = mysql_real_escape_string($_POST['name']);
$city = mysql_real_escape_string($_POST['city']);
$country = mysql_real_escape_string($_POST['country']);


$name = strip_tags($_POST['name']);
$city = strip_tags($_POST['city']);
$country = strip_tags($_POST['country']);



// Anti-SQL Injection
function check_inject()
  {
    $badchars = array(";", "'", "\"", "*", "DROP", "SELECT", "UPDATE", "DELETE", "-");
 
    foreach($_POST as $value)
    {
      if(in_array($value, $badchars))
      {
        filelogs("injection", "user", $_SERVER['REMOTE_ADDR']);
        die("SQL Injection Detected\n<br />\nIP: ".$_SERVER['REMOTE_ADDR']);
      }
      else
      {
        $check = preg_split("//", $value, -1, PREG_SPLIT_OFFSET_CAPTURE);
        foreach($check as $char)
        {
          if(in_array($char, $badchars))
          {
            filelogs("injection", "user", $_SERVER['REMOTE_ADDR']);
            die("SQL Injection Detected\n<br />\nIP: ".$_SERVER['REMOTE_ADDR']);
          }
        }
      }
    }
  }
// File Logger
function filelogs($type, $info, $muser) {
$agent = $_SERVER['HTTP_USER_AGENT'];
    $uri = $_SERVER['REQUEST_URI'];
    $ip = $_SERVER['REMOTE_ADDR'];
    $ref = $_SERVER['HTTP_REFERER'];
    $dtime = date('r');
   
    if($ref == ""){
        $ref = "None";
    }
    if($muser == ""){
        $muser = "None";
    }
    $location = "/";
    $type = $location . $type . ".txt";
    $entry_line = "$dtime - IP: $ip | Agent: $agent  | URL: $uri | Referrer: $ref | Username: $muser | Query : $info \n";
    $fp = fopen("$type", "a");
    fputs($fp, $entry_line);
    fclose($fp);
}

   
if (empty($_REQUEST['step'])) $step = 1; else $step = $_REQUEST['step'];


include_once ("config.php");

if (!empty($_POST))
{
if ($step < 3) // insert/update info
{
  $fields = $values = array();
 
  unset($_POST['Submit']);
 
  if (empty($_POST['id']))
  {
  unset($_POST['id']);
  foreach ($_POST as $field=>$value)
  {
    $fields[] = $field;
    $values[] = '"'.htmlspecialchars(trim($value)).'"';
  } 
  $query = 'INSERT INTO Profile_submission ('.implode(',', $fields).') VALUES ('.implode(',',$values).')';
  mysql_query($query);
  $id = mysql_insert_id();

  # set cookies
  if (!empty($id)) setcookie('authcode', $id, time() + 3600*24*365, '/');
 
  }
  else
  {
  $qryString = array();
  $currentID = $_POST['id'];
  unset($_POST['id']);
  foreach ($_POST as $field=>$value)
  {
    $qryString[] = $field.' = "'.htmlspecialchars(trim($value)).'" ';
  } 
  $query = 'UPDATE Profile_submission SET '.implode(',', $qryString).' WHERE sid = "'.$currentID.'"';
  mysql_query($query);
  }
}
else // upload photos
{
$uploaded_file ="";
  // move uploaded file
  if ($_FILES['picture']['tmp_name'] != "none" and $_FILES['picture']['tmp_name'] != "")
  {
  $tmpname = rand(time()-10000, time()).".jpg";
  $uploaded_file = 'uploads/'.$tmpname;
                             
                           
  if (@move_uploaded_file($_FILES['picture']['tmp_name'], $uploaded_file))
  {
    chmod($uploaded_file, 0777);
  } 
  }
$query = 'UPDATE Profile_submission SET picture'.($step-2).' = "'.$uploaded_file.'" WHERE sid = '.$id;
  mysql_query($query);
}
}
elseif (!empty($_COOKIE['authcode']))
{
$query = 'SELECT * FROM Profile_submission WHERE sid = "'.$_COOKIE['authcode'].'"';
$result = mysql_query($query);
if (mysql_num_rows($result))
{
  $profile = mysql_fetch_assoc($result);
  $id = $_COOKIE['authcode'];
}
}


if ($step > 6) {
header("Location: http://");
}

?>

[wildteen88]

before you use mysql_real_escape_string make sure you are connected to mysql first. mysql_real_escape_string requires you to be connected to mysql in order for this function to work. From looking at your code you connect to mysql way after you use mysql_real_escape_string.

[redarrow]

another way ok.
[code]

<?php

$name = addslashes($_POST['name']);
$city = addslashes($_POST['city']);
$country = addslashes($_POST['country']);

?>

[/code]

[.josh]

[code]
<?php
//the variables below are an abbreviated list
name = trim($_POST['name']);
$city = trim($_POST['city']);
$country = trim($_POST['country']);

$name = mysql_real_escape_string($_POST['name']);
$city = mysql_real_escape_string($_POST['city']);
$country = mysql_real_escape_string($_POST['country']);


$name = strip_tags($_POST['name']);
$city = strip_tags($_POST['city']);
$country = strip_tags($_POST['country']);
[/code]
you keep overwriting your previous variables with your new variables, because you use the same $_POST in each new php function call, instead of using the new and altered data.  example:

$name = trim($_POST['name']);

you are making a variable called $name, trimming $_POST['name'] and assigning the result to $name.

then in the next step, you are taking this same $name, mysql_escape_real_stringing it, but instead of using your trimmed variable, you are using the original $_POST['name'].  so when all is said and done, all you've really done is strip_tagged the original $_POSTed data.

what you should be doing is something like this:

[code]
<?php
$name = trim($_POST['name']);
$name = mysql_real_escape_string($name);
$name = strip_tags($name);
?>
[/code]

also to re-iterate what wildteen said too: you need to establish a db connection before you can use mysql_real_escape_string.  move your include('config.php'); up to somewhere before calling that function.

[Jenk]

mysql_real_escape_string() is all you need to make a variable safe for inserting to mysql. strip_tags() is not necessary (and is not favored over htmlentities(),) trim is just not necessary.

Escaping characters only turns them to literal values. You will not see the escaping character ("\") in your MySQL database. Inserting a value of: O'Reilly (when escaped will appear as O\'Reilly) will appear in your database as O'Reilly.

If you do not escape, the query will fail.

[russia5]

Thankyou very much!  I took something from all the posts and made it work!  I moved the MySQL connection to the top, deleted the addslashes() so now all I have is the mysql_real_escape_string() so the variables are not being overwritten and it works fine.  I understand from the posts, that the way you know it works, is that you are not getting an error.  (and I am not) Thankyou very much for your help! Greg