problem with php mysql query...

Question

I have a form with method = post submitting to a form handler file which is writing the post to a DB and then emailing the result to a user.

I have written an insert statement but when the form is being processed I'm getting an error, but not being a DB whiz I'm not really sure what the problem is. Here's the code--

// MYSQL QUERY
$result = mysql_query("INSERT INTO 2009_prize_results (name, address, address2, email, 100, 101, 102, 103, 104, 105, 106, 107, 108) VALUES ($_POST[name],$_POST[address],$_POST[address2],$_POST[email],$_POST[100],$_POST[101],$_POST[102],$_POST[103],$_POST[104],$_POST[105],$_POST[106],$_POST[107],$_POST[108])");
if (!$result) {
    die ("SQL error: " . mysql_error());
}

And the error I'm getting is--

SQL error: 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 '100, 101, 102, 103, 104, 105, 106, 107, 108) VALUES (Marty Martin,313 Orlando Av' at line 1

The submission values were

name = Marty Martin
address = 313 Orlando Ave
address2 = Anytown, VA
email = [email protected]
100 = on
101 = off
10* are all checkboxes so are either on or off

What do I have wrong here?

Answer 1

You need to put your values in quotes. For example:

... VALUES(Marty

should be

... VALUES('Marty'

Try:

// Concatenated for readability
$query = "INSERT INTO 2009_prize_results " .
         "(name, address, address2, email, 100, 101, 102, " . 
         "103, 104, 105, 106, 107, 108) VALUES " . 
         "('{$_POST['name']}', 
          '{$_POST['address']'}, 
          '{$_POST['address2']}', 
          '{$_POST['email']}', 
          '{$_POST[100]'}, '{$_POST[101]}', '{$_POST[102]}', '{$_POST[103]}', 
          '{$_POST[104]}', '{$_POST[105]}', '{$_POST[106]'}, '{$_POST[107]}', 
          '{$_POST[108]'})";

$result = mysql_query($query);

Answer 2

you need single quotes around each item in VALUES

EDIT: also your $_POST[] variables might not evaluate while in a string. You need to use a different syntax or use the evil extract();

Answer 3

The way to debug these sorts of problems is to take a look at the query actually being sent and see what is wrong with it. When you see what is wrong with it, you can figure out what value is globbing up the works and fix it with the appropriate adjustments and/or validation checks.

Also, keep in mind: strings get surrounded by '' (single quotes), and values that aren't specified for strings should be passed in as NULL (no quotes). And if you have table or column names that are non-standard or otherwise problematic, don't forget `` (backticks) around its name; those can cause syntax errors as well.

Answer 4

If you actually have columns with numeric names like that, you likely need to delimit them with backticks so they aren't interpreted as literal numbers by SQL. And please, for your own sanity and mine, format your queries.

$result = mysql_query("
INSERT INTO 2009_prize_results(
       name
     , address
     , address2
     , email
     , `100`
     , `101`
     , `102`
     , `103`
     , `104`
     , `105`
     , `106`
     , `107`
     , `108`
)
VALUES (
    -- values here as others have stated
)");

Answer 5

In addition to wrapping variables in single quotes, you should also escape special characters in your POST variables. Take a look at the mysql_real_escape_string method documentation for an explanation and examples.

On preview, escaping is done to help prevent SQL injection attacks of the sort that XKCD makes fun of. Except that it's no fun when it happens to you and is easily avoidable.

To answer the commenter's question, yes, you can also perform prepared statements with PHP and MySQL. This is an alternative to using the mysql_real_escape_string method.