MySQL INSERT error

[Jacob Lyles]

Howdy,

I'm a but of a newbie and I'd appreciate some help with a MySQL issue
I'm having. I'm trying to insert some data into MySQL from a POST form
but the query breaks whenever a user fails to fill out a value. Here's
the PHP code that breaks when the $_POST[opus] variable is left blank:

mysql_query("IN SERT INTO piece VALUES (NULL, '$_POST[composer]',
$_POST[opus], $_POST[year], '$_POST[name]' , '$_POST[work_type]',
$_COOKIE[user_cookie])") or die(mysql_error ());

Here's my error message:

"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 '1865,'Symphony No. 2 in B-flat Major','1',15)' at line 1"

And lastly, here's the settings of the mySQL field:

opus is a mediumint of length nine. NULL = yes. Default = NULL.

Thanks,
Jacob

[joshbeall@gmail.com]

Re: MySQL INSERT error

Looks like you're not escaping the POST'd data, which is very
dangerous, unless you are automagically escaping incoming data.

Anyway, I'd suggest storing your query in a string, then echoing it
before it is executed so you can see the entire query, Then you might
be able to see where the problem is. Nothing jumps out at me after a
cursory glance at your code.

[joshbeall@gmail.com]

Re: MySQL INSERT error

Escaping data: http://php.net/mysql_real_escape_string

[Gordon Burditt]

Re: MySQL INSERT error

>I'm a but of a newbie and I'd appreciate some help with a MySQL issue[color=blue]
>I'm having. I'm trying to insert some data into MySQL from a POST form
>but the query breaks whenever a user fails to fill out a value. Here's[/color]

Then don't submit the query if the user fails to fill in a value.
Give the user an error message. Or if it's legitimate and you
want to insert a null value, replace the unset value with the
word null (for integers where you're not enclosing the value in
quotes).

You're also begging for a SQL injection attack here. And
happens if $_POST['name'] is:
Beethoven's 5th symphony
which will also cause SQL errors?

If you take input from the browser (which includes anything from
$_GET, $_POST, $_REQUEST, or $_COOKIE) and put it into SQL
without at a minimum quoting it first (e.g. with addslashes()
or mysql_escape_st ring()), you're in trouble. If user input
(especially a single or double quote as part of the input)
can cause SQL errors, you're in trouble.
[color=blue]
>the PHP code that breaks when the $_POST[opus] variable is left blank:
>
>mysql_query("I NSERT INTO piece VALUES (NULL, '$_POST[composer]',
>$_POST[opus], $_POST[year], '$_POST[name]' , '$_POST[work_type]',
>$_COOKIE[user_cookie])") or die(mysql_error ());
>
>Here's my error message:
>
>"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 '1865,'Symphony No. 2 in B-flat Major','1',15)' at line 1"[/color]

Gordon L. Burditt