2

I found the issue and now my insert query works, but I don't understand why. Here is my current working query:

$add_movie = mysql_query("INSERT INTO ownedmovies VALUES ('', '{$movie_data['Title']}',
    '{$movie_data['Year']}', '{$movie_data['Rated']}', '{$movie_data['Runtime']}',
    '{$movie_data['Genre']}', '{$movie_data['Director']}', '{$movie_data['Writer']}',
    '{$movie_data['Actors']}', \"{$movie_data['Plot']}\", '{$movie_data['imdbRating']}')");

Notice that I used double quotes around the plot field and normal around everything else. When I did the plot field the same way as the others, it would not error but nothing would get inserted into the table... now it works perfectly.

Could anyone enlighten me on why this is?

Thank you

betabandido
  • 18,946
  • 11
  • 62
  • 76
Troy Cosentino
  • 4,658
  • 9
  • 37
  • 59
  • 2
    Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is good PDO tutorial](http://goo.gl/vFWnC). – Madara's Ghost Jul 06 '12 at 21:27
  • You are not doing any error checking in your query so it's no wonder it won't give an error. See here on how to do proper error checking. http://stackoverflow.com/questions/6198104/reference-what-is-a-perfect-code-sample-using-the-mysql-extension – Pekka Jul 06 '12 at 21:27
  • possible duplicate of [Difference between single quote and double quote string in php](http://stackoverflow.com/questions/3446216/difference-between-single-quote-and-double-quote-string-in-php) – Madara's Ghost Jul 06 '12 at 21:28
  • 1
    @Truth Not a duplicate of that (though I wouldn't be surprised if there were another dup of this question floating around). This is about how mysql handles double vs. single quotes, not PHP. – octern Jul 06 '12 at 21:39
  • 2
    Let's just pray really hard that `$movie_data['imdbRating']` never contains a string like "`1'); DELETE FROM ownedmovies WHERE ('a'='a`" – spencer7593 Jul 06 '12 at 21:41
  • possible duplicate of [What is the difference between single and double quotes in SQL?](http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql) – vyegorov Jul 06 '12 at 21:47
  • Also worth checking: [What does the SQL Standard say about usage of `?](http://stackoverflow.com/questions/10573922/what-does-the-sql-standard-say-about-usage-of) – vyegorov Jul 06 '12 at 21:49

4 Answers4

5

I suspect that your plot string contains a single quote. To avoid the problem you should be escaping your string values using mysql_real_escape_string, or (better) use parameterized queries.

Your "solution" of changing the single quote to a double quote may appear to work in this case, but it will fail if the plot string contains a double quote.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • +1 This is the most logical explanation for the behavior: the string contains a single quote which is not being escaped. It raises the usual warnings about thwarting SQL injection attacks. – spencer7593 Jul 06 '12 at 21:47
  • Oh ok that makes sense now i didn't even think of that. Sorry about the stupid mistake, just learning php/mysql. And i will look into the notes posted above as well, thank you everyone! – Troy Cosentino Jul 06 '12 at 23:49
1

In PHP double quotes will parse variables in side it, the single quotes inside them will just be single quotes in the string.

To pass varchar parameters to mysql, they need to be enclosed in quotes, so the single quotes are getting passed to mysql as the varchar parameters.

Your php variables are being parsed inside the double quotes and the sql string will contain the values you pass it in the variables.

databyss
  • 6,318
  • 1
  • 20
  • 24
1

Take care when using double quotes in MySQL queries - they have different meanings depending on the SQL mode that MySQL is running in.

select "fred" from table

Will return the values from the column fred when in ANSI_QUOTE mode otherwise it will return the literal value fred.

noz
  • 1,863
  • 13
  • 14
0

Double and single quotes are used together to keep the program from confusing where the query ended.

for example a query

 ("Select 'name' From 'contacts'")

If you used all double quotes, then it would look like this

 ("Select "name" From "contacts"")

And the program could be let to think the actual query is "Select "

That is how I think it is.

Web Master
  • 4,240
  • 6
  • 20
  • 28