mysql_real_escape_string()

@dai_hopOct 10.2008

Hi All,

I have a problem with some code that uses the mysql_real_escape_string() function.

Because of issues with PHP sessions and load balancing I had to modify session handling so data is now saved to a database instead.

I’m using the code from this article:

~~[URL=”http://www.devshed.com/c/a/PHP/Storing-PHP-Sessions-in-a-Database/6/”]~~http://www.devshed.com/c/a/PHP/Storing-PHP-Sessions-in-a-Database/6/[/URL]

The problem is, pages that use session variables now output the following error messages…

~~[QUOTE]~~

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) in /home/nas04l/m/midasprint.com/user/htdocs/functions/sessions.php on line 53

[/QUOTE]

…and…

~~[QUOTE]~~

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/nas04l/m/midasprint.com/user/htdocs/functions/sessions.php on line 53

[/QUOTE]

Whats wrong and how do I go about fixing it?! :S

to post a comment

PHP

14 Comments(s) _↴

@MrCoderOct 10.2008 — #You need to have a valid mysql link before calling mysql_real_escape_string()

Use mysql_connect() before calling it.

Why they had to name one of the most used functions something so long amuses me.

Surely mysql_clean() would of been enough?

@ariellOct 10.2008 — #True, and a good idea. So, we should define soemthing like

[code=php]
 function sql_clean($val)       {
 return(mysql_real_escape_string($val));
 }
 [/code]

At least this saves a lot of typing (and thus typos).

@dai_hopauthorOct 10.2008 — #Thanks for the replies! ?

I already have a function I use to query my database. It establishes the connection, runs the query, and then closes the connection.

Considering this, and also that my new session handling code is in another file that is referenced via a "require_once", what do you guys think would be the best way to establish the link for this function?

@ShortsOct 10.2008 — #I concur, all my database stuff is a big class. mysql_real_escape_string() is done like so:

[code=php]
 <?php
 public function anti_hack($string=false,$strip_tags=false) {
 if(!$string) { return ''; }
 if($this->flagged == false) {
 # DOES STUFF HERE IF A STRING LOOKS LIKE A POSSIBLE INJECT
 }
 if(!$string_tags) { $string = strip_tags($string); }
 if(get_magic_quotes_gpc()) { $string = stripslashes($string); }
 if(function_exists('mysql_real_escape_string')) { $string = mysql_real_escape_string($string); }
 else { $string = addslashes($string); }
 return $string;
 }
 ?>
 [/code]

So everything looks like:

[code=php]
 <?php
 $sql = $db->query("SELECT * FROM table WHERE username='{$db->anti_hack($_POST['username'])}' AND password='{$db->anti_hack($_POST['username'])}'");
 $user = $db->fetch_array($sql);
 ?>
 [/code]

Besides making things look cleaner I have flags in place, besides $db->anti_hack() also have $db->get_int() and $db->get_float that makes sure input is an int or float and if not just flags them in a database. Then I get to see if people are trying to be up to no good and all the info I can get about them.

@NogDogOct 10.2008 — #Perhaps the "best" solution would be to use the MySQL[b]i[/b] extension and use prepared statements with bound parameters, then not have to worry about explicit escape functions. ?

@ShortsOct 10.2008 — #Perhaps the "best" solution would be to use the MySQL[b]i[/b] extension and use prepared statements with bound parameters, then not have to worry about explicit escape functions. ?[/QUOTE]

Pfft, I might as well be using PERL! But then again I do love PERL and the $sth->prepare() was my bestest friend at the time. Sad, I know...

Preparing is probably a tad bit too complex for a bunch of people though. Even though its not at all :]

@NogDogOct 10.2008 — #Pfft, I might as well be using PERL! But then again I do love PERL and the $sth->prepare() was my bestest friend at the time. Sad, I know...

Preparing is probably a tad bit too complex for a bunch of people though. Even though its not at all :][/QUOTE]

Eh, anyone who can learn to implement any non-trivial MySQL query in PHP has the skills to use the MySQLi functions. At a minimum, they can at least implement them in procedural mode, though we all should be learning to code in object-oriented style, right? ?

@ariellOct 10.2008 — #I STRONGLY AGREE! Looking over code - wherever you do - is somewhat shocking for it seems to me that OO to many people still feels "extravagant" or "artificial". But it's quite the contrary, there is nothing more NATURAL than considering things as objects thus programming strict OO.

Thanks to nog & best from the south.

@ShortsOct 10.2008 — #Nog, I've been thinking about it quite a bit, just bought a domain name 00ff00withenvy.com. I'm totally going to program the backend in PERL. I miss using it.

And yes, glad that PHP is getting more OOP oriented, trying to get more and more OO in all aspects of my computer existence. Especially working with a team on a project.

@MrCoderOct 16.2008 — #It establishes the connection, runs the query, and then closes the connection.[/QUOTE]

Why not keep it open until all queries have been executed?

@dai_hopauthorOct 16.2008 — #I did in the end, this was my solution...

[code=php]<?php
 
 /*
 
 PHP code used to handle session data via a database.
 Original code found at: http://www.devshed.com/c/a/PHP/Storing-PHP-Sessions-in-a-Database/6/
 
 */
 
 // specify includes
 require_once('db.php');
 
 // opens a connection to the db
 function openConn()
 {
 $varArr = dbVars('daisy');
 $conn = mysql_connect($varArr['hostname'], $varArr['username'], $varArr['password']) or die ("Error: Failed to establish database connection.");
 mysql_select_db($varArr['database'], $conn) or die ("Error: Database selection failed.");
 return $conn;
 }
 
 // closes a connection to the db
 function closeConn($conn)
 {
 mysql_close($conn);
 }
 
 // --------------------
 
 class SessionManager
 {
 var $life_time;
 
  // initalise new session manager
  function SessionManager()
  {
   $this->life_time = get_cfg_var("session.gc_maxlifetime");
   session_set_save_handler( 
   array( &$this, "open" ), 
   array( &$this, "close" ),
   array( &$this, "read" ),
   array( &$this, "write"),
   array( &$this, "destroy"),
   array( &$this, "gc" )
   );
 
  }
 
  // open session
  function open($save_path, $session_name)
  {
   global $sess_save_path;
   $sess_save_path = $save_path;
   return true;
  }
 
  // close session
  function close()
  {
   return true;
  }
 
  // read session data from the db
  function read($id)
  {
   $conn = openConn();
   $q = "SELECT session_data FROM sessions WHERE session_id = '" . mysql_real_escape_string($id) . "' AND expires > " . time();
   $result = mysql_query($q, $conn);
   closeConn($conn);
   if (mysql_num_rows($result) > 0)
   {
   $row = mysql_fetch_array($result);
   return $row['session_data'];
   } else
   {
   return '';
   }
  }
 
  // write session data to the db
  function write($id, $data)
  {
   $time = time() + $this->life_time;
   $conn = openConn();
   $q = "REPLACE sessions (session_id, session_data, expires) VALUES ('" . mysql_real_escape_string($id) . "', '" . mysql_real_escape_string($data) . "', " . $time . ")";
   mysql_query($q, $conn);
   closeConn($conn);
   return true;
  }
 
  // remove session data from the db
  function destroy($id)
  {
   $conn = openConn();
   $q = "DELETE FROM sessions WHERE session_id = '" . mysql_real_escape_string($id) . "'";
   mysql_query($q, $conn);
   closeConn($conn);
   return true;
  }
 
  // remove expired session data from the db (garage collection)
  function gc()
  {
   $conn = openConn();
   $q = "DELETE FROM sessions WHERE expires < UNIX_TIMESTAMP()";
   mysql_query($q, $conn);
   closeConn($conn);
   return true;
  }
 }
 
 ?>[/code]

It seams to work really well so far!

@felgallOct 16.2008 — #If you actually validate the input fields you receive properly in the first place then there will be very few that will need to be passed through mysql_real_escape_string() since the characters it converts are not valid input for most fields and therefore should not pass validation.

@ariellOct 17.2008 — #You should at least check whether or not the mysql_XXX functions return properly and, depending what they "said", return true or false. Returning true by default looks pretty error-prone.

@MrCoderOct 17.2008 — #Open your mysql connection on the construction of your class, not every function call?

Also don't forget to use typecasting in place of mysql_real_escape_string calls when possible.

Also in #PHP _↴

How to slice ass array ?Send file on Sever via Email Form Small adjustment

Success!

Help @dai_hop spread the word by sharing this article on Twitter...

Tweet This

mysql_real_escape_string()

14 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

mysql_real_escape_string()

14 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

14 Comments(s) _↴

Also in #PHP _↴