Using Table Names With Spaces in PHP and mySQL

The key to writing SQL queries using table names that contain spaces is to make sure you use the correct quotes.

This is wrong:


SELECT * FROM tablename ORDER BY ''field name'' ASC

This is correct:


SELECT * FROM tablename ORDER BY `field name` ASC

These (`) are called back ticks.

Also it is a good idea to make sure there are no MySQL special characters in the variable name like in the following example:


SELECT * FROM tablename ORDER BY `$name` ASC

Use the following to make sure that this is not a problem:


function returnDBFieldEscaped($theData){
     $theData = preg_replace("/(\\n|\\r)/"," ",$theData);
     $theData = (trim(addslashes($theData)));
      if (str_empty($theData)){
          return "";
     } else{
           return $theData; 
     }
}
//Example:
$name = returnDBFieldEscaped($_POST['order_by']);
$query = "SELECT * FROM tablename ORDER BY `{$name}` ASC";

It will remove common characters that are not allowed and escape special characters in the unescaped string, so that it is safe to place it in a MySQL query. It may not handle all cases. Please leave a comment, if you have any improvements to suggest.