mystery mysql error

Question

Hello all. I'm by no means experienced in mysql and keep getting an error in this lines of code:

$sql= "INSERT INTO songs (unique_show_id, artist, date, year, city, state, venue, taper, transfered_by, source, mic_loc, lineage, uploaded_by, uploaded_on, show_notes, show_xml)
            VALUES('$showId', '$artist', '$showDate', '$year, '$city', '$state', '$venue', '$taper', '$transferer', '$source', '$mic_loc', '$lineage', '$uploader', NOW(), '$show_notes', '$show_xml')";    

//check to see if the query went through
            if (!mysql_query($sql,$con)){
              echo "query fail";
            die('Error: ' . mysql_error());
    }

I'm sure it's something simplistic, but I can't see where the error is. The error message I get is:

query failError: 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 'ipuhgbi', 'CA', '', '', '', '', '', '', 'danwoods', NOW(), '', '<show id=\'gm198' at line 2  

Some of the values I'm inserting are NULL, but from what I've read I don't think that should be a problem. Any ideas?

Answer 1

You need to use mysql_real_escape_string() in each and every single one of your $variables.

Also, read this StackOverflow question carefully regarding SQL Injections.

Answer 2

It looks like the last single quote on the error line is not escaped.

you need to remember to sanitize all of the strings going into the query.

Answer 3

There are quite few things you need to be sure about:

1. You don't insert primary keys through queries (eg unique_show_id in your code)
2. For numbers you don't use single quotes.
3. It is better to use the set variant of inserting records which avoids count problems eg:

4. Use intval for numbers and mysql_real_escaps_string for strings to avoid injections issues as well as single quotes query erros.

   insert into table set field='field_value', field2='field_value' // and so on

Answer 4

Missing quote after $year.

When MySQL issues such an error (near bla di bla), the error is usually immediately before the string it mentions. In this case 'ipuhgbi' maps to $city, so you know it's right before '$city', and what do we see there? Voila, a missing quote.