MySQL Unknown column in where clause for simple select statement (SOLVED)-Collection of common programming errors

I have a very basic select statement that is causing a column unknown error. The problem with the query happens when I try to use a character instead of just numbers in the variable. Wondering if it has anything to do with Collation.

Here’s what I have so far:

$titleno=$_REQUEST['title_no'];
$titleno=mysql_real_escape_string($titleno);
$titleno = utf8_decode($titleno); //tried without this before but didn't work
$query="SELECT * FROM `Titles` WHERE `title-no` = '".$titleno."'"; 
//tried various versions of this query - left it as single quotes as that seems to be the correct way. This only fails when a character is entered. Numbers work fine.  

echo "query - 
 $query 
";    
$get_title_result=mysql_query($query) or die(mysql_error());
//here I get the unknown column name error - MySQL treats the titleno as the column name 

Echo output:

SELECT * FROM `Titles` WHERE `title-no` = '1234566d' 
Unknown column '1234566d' in 'where clause'

If I didn’t use the ‘d’ in title-no, it works fine….Also, I tried a different column name that doesn’t have the hyphen and still get the same behavior. The DB defines collation for title-no as latin1_swedish_ci. (This problem doesn’t occur when I paste the query into mysqladmin)

Here’s the table definition:

CREATE TABLE `Titles` (  
 `id` int(11) NOT NULL auto_increment,  
 `title-no` varchar(15) NOT NULL,  
  UNIQUE KEY `title-no` (`title-no`),  
  KEY `id` (`id`)  
  ) ENGINE=MyISAM  
  AUTO_INCREMENT=9090949 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9090949 ;

RESOLVED: The issue was not with this query. It was with a subsequent query. I was confused because I was only echoing this query. My bad. Thank you all for your support! 🙂

1. Try with:

   $query = "SELECT * FROM Titles WHERE `Titles`.`title-no` = '" . $titleno . "'"; 
   

2. Here is a quick conversion to statement-based query (which is using MySQLi, adapt as necessary, your code or this example). The assumption is that the underlying prepared statement engine knows that you cannot specify a column name with placeholders in a prepared statement, so it should be passing it correctly (here’s hoping 🙂

   $titleno=$_REQUEST['title_no'];
   $statement=mysqli_prepare($your_mysqli_link, "SELECT `id` FROM `Titles` WHERE `title-no` = ?"); 
   mysqli_stmt_bind_param($statement, 's', $titleno);
   mysqli_stmt_execute($statement);
   mysqli_stmt_bind_result($statement, $found_id);
   mysqli_stmt_fetch($statement);
   echo "found id: $found_id";
   

Originally posted 2013-11-10 00:14:23.