is mysql_real_escape_string() needed?

[ppowell]

I have a case whereby I saw a developer doing this:

class MySQLQuery {

/*------------------------------------------------------------------------------------------------------------------------------------
	This legacy class will perform queries and return results if applicable, also free results and returns affected
	rows also if applicable
--------------------------------------------------------------------------------------------------------------------------------------*/

/**
 * @access private
 * @var mixed $sql
 */
var $sql;			// SQL STRING PROPERTY

/**
 * MySQL connection
 * 
 * @access private
 * @var resource $mySQLConn
 */
var $mySQLConn; 	// MySQLQuery CLASS OBJECT RESOURCE LINK PROPERTY	

/**
 * Constructor
 *
 * @access public
 * @param mixed $sql
 * @param resource $mySQLConn
 */
function MySQLQuery($sql, $mySQLConn) {				// CONSTRUCTOR
	$this->sql = $sql;
	$this->mySQLConn = $mySQLConn;
}

/**
 * Run query
 *
 * @access public
 * @return object $result (optional)
 */
function runQuery() {										// RESULT RESOURCE LINK METHOD
	$result = @mysql_query($this->sql, $this->mySQLConn);
	/*-----------------------------------------------------------------------------------------------------------------------------
		runQuery() method will be used for all SQL statements.  getResult() will also run this method for
		"SELECT" SQL statements which will return a resultset or an empty resultset if nothing is found.
		You need to specifically flag for "SELECT" SQL statements that throw a mySQL error however, since
		non-SELECT statements will return a boolean instead of a resultset.
	-------------------------------------------------------------------------------------------------------------------------------*/
	if (mysql_errno() && preg_match('/^select[ ]+/i', $this->sql)) trigger_error(mysql_error() . ' using query: ' . $this->sql, E_USER_ERROR);
	return $result;
}

}


$query =& new MySQLQuery($sql, $this->dbAP->getDBConn());
if (@!$query->runQuery() || mysql_error()) echo mysql_error(); else echo "updated";

This should trigger an error if the SQL query is mal-formed, such as:

// $_POST['name'] = "Martiin O'Malley";
$sql = "UPDATE table SET name = '" . $_POST['name'] . "' WHERE id = 1";

However, I'm somehow able to get away with that without having to do this:

// $_POST['name'] = "Martin O'Malley";
$sql = "UPDATE table SET name = '" . mysql_real_escape_string($_POST['name']) . "' WHERE id = 1";

Is there now a case in PHP whereby string values put into SQL transactions do not have to be escaped by either mysql_escape_string() or mysql_real_escape_string()? This looks highly unsafe but I'm totally baffled as to how I am able to get away with a perfect MySQL 4.1.20 table updating with mal-formed SQL!

Thanx
Phil

[sneakyimp]

you probably have magic quotes on which would turn

Martin O'Malley

into

Martin O\'Malley

To find out:

if (get_magic_quotes_gpc())
  echo "MAGIC QUOTES ARE ON"
else
  echo 'no magic quotes';

mysql_real_escape_string() is what i always try to use. the reason being that it's language sensitive? that would mean that if someone were to use a double-byte char representation of a double or single quote mark in a hack attempt then this would be properly escaped--at least i think so.

embarrassingly, i just realized i'm not sure exactly what 'language sensitive' might mean. i'm guessing that while mysql is running, there's a certain charset and that what passes as valid sql depends somewhat on that charset inasmuch as what passes for quotes is determined by the charset.

anybody care to elaborate on this?

[madwormer2]

mysql_real_escape_string() uses MySQL itself.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

That from the manual pretty much sums it up.