Using mysql_real_escape_string() on string data will only prevent the sql syntax from breaking and will prevent sql injection via string data. SQL can still be injected in numeric data (see RussellReal's post above.)
Someone can still trigger mysql errors by doing things like injecting a large amount of data that exceeds the max_allowed_packet or can cause 'server has gone away' errors.
In general, you should not cause the output from mysql_error() to appear on a live web site (display_errors should be OFF and log_errors should be ON and your logic should cause mysql_error() to use a php function that makes use of the error_reporting/display_errors/log_errors settings) and you should use error checking logic on all your queries (and in fact on all functions that can fail) to detect if an error occurred, output a meaningful user error message when it does, report/log system level information so you can find and fix problems, and take an appropriate action in the remainder of your code on the page.
Typical logic to do this for SELECT, SHOW, DESCRIBE, EXPLAIN... type queries -
<?php
$query = " ... "; // your query in a string variable (makes error reporting easier because you can log or display the actual query)
// execute the query and check for success or failure
if($result = mysql_query($query)){
// the query executed without any errors and returned a result resource (SELECT, SHOW, DESCRIBE, EXPLAIN... type queries)
// check if any rows were returned by the query (SELECT... type queries)
if(mysql_num_rows($result)){
// the result set contains one or more rows
// process the row(s) in the result set here...
} else {
// no rows were matched, output a helpful user error message
echo "The query did not match any rows in the database<br />";
}
} else {
// the query failed and returned a FALSE value
// do your application level error reporting here...
trigger_error("Query failed: $query<br />" . mysql_error());
// output a user error message
echo "The query could not be executed at this time due to an error...<br />";
}
// the remainder of the code on the page having nothing to do with the above query logic
?>
Typical logic to do this for INSERT, UPDATE, DELETE, DROP... type queries -
<?php
$query = " ... "; // your query in a string variable (makes error reporting easier because you can log or display the actual query)
// execute the query and check for success or failure
if($result = mysql_query($query)){
// the query executed without any errors and returned a TRUE value (INSERT, UPDATE, DELETE, DROP... type queries)
// check if any rows were affected by the query (INSERT... type queries)
if(mysql_affected_rows()){
// one or more rows were affected, do success processing...
// output a success user message -
echo "The row was successfully UPDATED in the table<br />";
} else {
// no rows were affected, do failure processing...
// output a user error message
echo "The query could not UPDATE the row in the table (likely because the WHERE clause is FALSE)<br />";
}
} else {
// the query failed and returned a FALSE value
// do your application level error reporting here...
trigger_error("Query failed: $query<br />" . mysql_error());
// output a user error message
echo "The query could not be executed at this time due to an error...<br />";
}
// the remainder of the code on the page having nothing to do with the above query logic
?>
