1

I've looked at a bunch of other threads with the same error, however mine seems to be a bit different. I've already checked for missing commas and stuff.

This is the original code that doesn't work:

SELECT `color_name`, `dna_sample` FROM `horse_color_base` WHERE `dna_sample` = $dna

However, if you change $dna to "eeee aaaa" (what the value of $dna is) it works perfectly.

SELECT `color_name`, `dna_sample` FROM `horse_color_base` WHERE `dna_sample` = 'eeee aaaa'

The validation error reads:

Failed to run query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'aaaa' at line 1

It seems like it's breaking up the $dna variable, which is screwing everything up.

Thoughts or ideas on how to make this thing work with the variable?

I'm using collation latin1_general_cs for the dna and dna_sample columns because I need them to be case sensitive.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Chelsea
  • 335
  • 2
  • 13
  • 1
    The variable is unquoted. See [When to use single quotes, double quotes, backticks](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) – Michael Berkowski Aug 31 '14 at 01:53
  • 1
    For this to work as you have it, the value of `$dna` would have to _literally_ include the quotes `$dna = "'eeee aaaa'";` which is very uncommon. More common is `WHERE dna_sample = '$dna'` – Michael Berkowski Aug 31 '14 at 01:54

1 Answers1

1

Like Michael Berkowski comments, quote the variable:

WHERE `dna_sample` = '$dna'

Writing a query this way is open to SQL injection. It's almost always better to write a query with bound parameters:

$stmt = $pdo->prepare('SELECT * FROM horse_color_base WHERE dna_sample = :dna');
$stmt->execute(array('dna' => $dna));
Community
  • 1
  • 1
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thanks so much guys. Every time I think I'm starting to get the hang of PHP, something weird like this happens. Helps to have a great StackOverflow community! – Chelsea Aug 31 '14 at 11:43