0

Can someone tell me why this query is wrong?

$tbl_name = "Attributes";
$pieces = //some array 
//other variables... blah blah blah

$query = "INSERT INTO $tbl_name (Word, What, When) VALUES";
foreach($pieces as $word){
    $query .= "('$word', '$What', '$When'),";
}

$query = substr($query, 0, -1);  //to remove the last comma

mysql_query($query) or die(mysql_error());

If you can tell, I am trying to insert multiple rows with a single query. When I try and run it, I get hit with a syntax error, but I am 99.9999% sure there are no spelling mistakes. Am I doing something wrong by trying to insert multiple rows at once like this?

Zack
  • 13,454
  • 24
  • 75
  • 113
  • 3
    `WHEN` is a reserved word in mySQL. Wrap the column name in backticks or use a different column name – Pekka Sep 13 '13 at 02:19
  • You're doing something wrong by not using bound parameters or (less preferable) sanitizing strings. It looks OK, print the SQL statement out & try it in your SQL tool to see what's wrong. – Thomas W Sep 13 '13 at 02:20
  • Good god. So simple a mistake. You saved me hours of work. – Zack Sep 13 '13 at 02:21
  • **By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. – Andy Lester Sep 13 '13 at 04:06

2 Answers2

2

when is a reserved word either wrap it in `` or use another. List of MySQL reserved words: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

  • Thank you!!!!!! It is funny to think that I used the variable "When" as an example by pure chance, potentially saving hours more work. – Zack Sep 13 '13 at 02:26
  • 2
    its a good thing to do once, you remember never to do it again! –  Sep 13 '13 at 02:27
0

is it possible that at some point in your $word,$what,$when you have a ' that is unsacaped ? or some mysql injection ?

if yes i would try using mysql_real_escape_string http://php.net/manual/en/function.mysql-real-escape-string.php

Nicolas Racine
  • 1,031
  • 3
  • 13
  • 35