尝试使用字母时从数据库中选择时未捕获的异常'ErrorException'

Question

I'm trying to select a value from the database, it works perfectly if everything is just numbers - but fails to work if there are letters involved. This is in OpenCart.

My column is called campaign_code , its type is VARCHAR.

Here is the command...

$campaign_id = $this->db->query("SELECT campaign_id FROM " . DB_PREFIX . "campaign WHERE campaign_code = ". $this->db->escape($campaign_code)); 

When $campaign_code equals any number it's fine. It selects as expected.

When its letters or a combination of numbers or letters I get this error...

Uncaught exception 'ErrorException' with message 'Error: Unknown column '85f' in 'where clause'<br />Error No: 1054<br />SELECT campaign_id FROM oc_campaign WHERE campaign_code = 85f' in C:\xampp\htdocs\microcharity\system\database\mysqli.php:40

Thank you.

Answer 1

While using an integer in where clause, you can avoid quotes but while using a string you have to enclose it in single quotes.

For integer -

$campaign_id = $this->db->query("SELECT campaign_id FROM '".DB_PREFIX."' WHERE campaign_code = ".$this->db->escape($campaign_code).""); 

For string -

$campaign_id = $this->db->query("SELECT campaign_id FROM '".DB_PREFIX."' WHERE campaign_code = '".$this->db->escape($campaign_code)."'");