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 = foo@foo.com
100 = on
101 = off
10* are all checkboxes so are either on or off

What do I have wrong here?

Please use proper formatting next time: http://stackoverflow.com/editing-help — Gumbo, Feb 27 '09 at 20:33
Use `bindparam` in all these cases as it prevents sql injection and also reduces any chances of error like these. — Ankit Jindal, Dec 06 '22 at 04:14

score 9 · Answer 1 · edited May 23 '17 at 12:17

9

Please see:

Little Bobby Tables :-) How does the SQL injection from the "Bobby Tables" XKCD comic work?

If you must use the mysql library then please be certain that you are running all of your inputs through mysql_real_escape_string, otherwise use Prepared Statements.

Then when you put the query together wrap all of the VALUES in quotes (single or double will work).

edited May 23 '17 at 12:17

Community

1
1

answered Feb 27 '09 at 20:39

Noah Goodrich

24,875
14
66
96

score 4 · Answer 2 · answered Feb 27 '09 at 20:40

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
)");

score 3 · Accepted Answer · answered Feb 27 '09 at 20:34

3

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();

answered Feb 27 '09 at 20:34

tkotitan

3,003
2
33
37

Thanks, that was part of the problem! – Marty Feb 27 '09 at 20:39

score 2 · Answer 4 · answered Feb 27 '09 at 20:35

2

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.

answered Feb 27 '09 at 20:35

Michael Trausch

3,187
1
21
29

thanks, you were right about the nonstandard names, having the column names in the DB as integers seemed to be causing a problem too. – Marty Feb 27 '09 at 20:40
$_POST['108'] = "'); DROP TABLE 2009_prize_results; --" – Pim Jager Feb 27 '09 at 22:28

Alex Reynolds · Answer 5 · 2009-02-27T22:37:29.690

2

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.

edited Feb 27 '09 at 22:37

answered Feb 27 '09 at 20:42

Alex Reynolds

95,983
54
240
345

Does MySQL still not have prepared statements/bindable parameters? I'm glad I don't have to use it – finnw Feb 27 '09 at 22:33

Ross · Answer 6 · 2009-02-27T20:38:57.400

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);

edited Feb 27 '09 at 20:38

answered Feb 27 '09 at 20:33

Ross

46,186
39
120
173

curses! 41 seconds faster than me! – tkotitan Feb 27 '09 at 20:35
See, this is a good example as to why PHP's embedded string substitution is a *bad* idea. – staticsan Mar 01 '09 at 23:37
Aye, a better approach could be to use sprintf (or even better: prepared statements). For simple things though the replacement offered by double-quotes can be very useful. – Ross Mar 02 '09 at 08:57

problem with php mysql query

6 Answers6