1

I need to do this query:

SELECT * FROM brands WHERE brand =`l'artigiano italiano`

Should be quite simple but it's not working. Like all my queries I try to execute it with the following code

$myDB = Database::getConnection($target='....',$key='....');

$sqlLogo = 'SELECT * FROM brands WHERE brand =`' . $brand->merk . '`';
$resultLogo = $myDB->query($sqlLogo);

When I open the page I get the following error

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'l'artigiano italiano' in 'where clause': SELECT * FROM brands WHERE brand =`l'artigiano italiano`; Array ( ) in merken_admin_settings() (line 23 of /home/mediaspe/domains/mediaspex.be/public_html/juniorsteps/sites/all/modules/merken/merken.admin.inc)

I tried to use

WHERE brand = "..."

and 

WHERE brand = `...`

but both did not work.

Any suggestions about the possible cause? I'm staring blind at the code at the moment.

Stephan Celis
  • 2,492
  • 5
  • 23
  • 38
  • You have been bitten by an SQL-injection leak. see: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain – Johan Aug 29 '11 at 16:30

5 Answers5

4

You should escape anything you embed in a SQL query. As you are using PDO, you should do it with $myDB->quote():

$sqlLogo = "SELECT * FROM brands WHERE brand = '" . $myDB->quote($brand->merk) . "'";

You can use both double quotes (") and single quotes (') for enclosing your values, but not back quotes (`).

It is even better to use prepared statements:

$stmt = $myDB->prepare("SELECT * FROM brands WHERE brand = :brand");
$resultLogo = $stmt->execute(array('brand' => $brand->merk));

This way you don't have to take care of escaping.

Arnaud Le Blanc
  • 98,321
  • 23
  • 206
  • 194
0

Have you tried escaping the single quote?

"l\'artigiano italiano"

MySQL String Syntax

TaylorOtwell
  • 7,177
  • 7
  • 32
  • 42
  • The problem that that value comes from the database, it's not a fixed value. But what I find weird is that when I copy paste the query from the error to mysql, the query works. But php doesn't want to execute it – Stephan Celis Aug 29 '11 at 16:26
0

While all of the other answers are valid for regular mysql_* functions, if you are using PDO, you should be taking advantage of prepared statements. They were made for just this purpose.

Mike
  • 23,542
  • 14
  • 76
  • 87
0

Just escaping the single quote can do the trick but try PDO prepared statements, they help you with security too (as already discussed here)
You can do it like this:

$brand = "l'artigiano italiano";
$stmt = $db->prepare('SELECT * FROM brands WHERE brand = :brand');
$stmt->execute( array(':brand' => $brand) );
Community
  • 1
  • 1
CaNNaDaRk
  • 1,302
  • 12
  • 20
0

It looks like you're using Drupal. If that's so (I assume so from the way you get the database connection and obviously the tag), you should execute your query like this:

$result = db_query('SELECT * FROM {brands} WHERE brand = :brand', array(':brand' => $brand));

That's all. Note:

  • Use of db_query(), which will call Database::getConnection() internally. You only need to call Database::getConnection() yourself if you want to connect to another than the default connection.

  • Use uf curly braces ({}) for tables managed by Drupal (which means, tables installed through hook_schema() of your module). If this is an external table not related to the Drupal installation, you won't need those. The curly braces ensure that the proper table name prefix is inserted in case the drupal installation was configured with one (which is for example the case when running tests!)

  • You can use :placeholder syntax for the dynamic values and directly specify the value in the second argument of db_query() (or $myMD->query(), that's the same interface).

Berdir
  • 6,881
  • 2
  • 26
  • 38