PHP Sanitation with MSQLI Real Escape String and STRTR Operators

Question

The following code is a proposal for a sanitation method when passing data to a remote database through SQL and PHP.

$name = strtr(mysqli_real_escape_string($connector_obj, $_POST['name']), '/\\', '   ');

My question is will the MSQLI operator sanitize the input then the strtr operator append making the code still safe, or is the field still vulnerable? This sanitation comes after the initial query, so the other option would be to pass the value to the database sanitized, an then do the strtr operation after the fact to output the info to customer-facing pages.

On a more high level view, how observant are users to the slashes in MSQLI escaped strings? I think it would look a little less appealing, and give insight into the structure of the application for those of ill repute, but any input on the idea would be appreciated.

Answer 1

mysqli_real_escape_string only escapes the characters. It does not sanitize.

When a value is Posted, that is the time to sanitize.

I prefer using integer values. Instead of email address as a user name I request phone number. Then use a regex to remove all but the numerical values.

$number = preg_replace('/[^\d]/','',$_POST['number']);

If I'm looking for a integer number with no spaces, commas, dashes I just use:

$number = intval($_POST['number']);

You should qualify values used in a query.

$sql =  sprintf("SELECT *  FROM `Client` WHERE `Number` = %d", $id);

I also look at everything that is submitted. Loooking for symbols and words that are typically used in SQL Injection attacks.

  $strike1 = 1 + preg_match_all('/\x3F/',$SAVE_ID,$matches, PREG_SET_ORDER);  // )
  $strike2 = preg_match_all('/[\x21-\x2F]|[\x3A-\x40]|[\x5B-\x60]|[\x7B-\x7F]/',$SAVE_ID,$matches, PREG_SET_ORDER);
  $strike3 = preg_match_all('/\x28/',$SAVE_ID,$matches, PREG_SET_ORDER);  // )
  $strike4 = preg_match_all('/\x29/',$SAVE_ID,$matches, PREG_SET_ORDER);  // ( 
  $strike5 =  preg_match_all('/COALESCE|0x|like|regex|mid|select|delete|drop|insert|do|call|replace|update|infile|lock|set|from|into|show|table|kill|reset/i',$SAVE_ID,$matches, PREG_SET_ORDER);

When I see too violations, I ban the IP address.

@mysql_unbuffered_query("INSERT INTO `Banned` (`ip`, `TimeStamp`,`Strike3`, `Attributes`) VALUES ('$ip', CURRENT_TIMESTAMP, $alert);");

I also watch for password failures. If I see too many failed attempts in a short amount of time, or the time between attempts is too short (not human) I ban the IP.