I have a SQL Syntax error on my php page

Question

Here is the mysql insert the I am running in php. I have removed the part giving the error but then I get a error on the next piece. I am not seeing what is diffrent to cause the error.

$fields="adv_exchange SET synum='".$synum."', worknum='".$_POST['worknum']."', user_id='".$current_user->ID."', f_name='".$current_user->user_firstname."', l_name='".$current_user->user_lastname."', email='".$current_user->user_email."', regnum=".$_POST['regnum'].", item='".$item."', qsver='".$_POST['qsver']."', flashrom='".$_POST['flashrom']."',expansion='".$_POST['board']."', rdisplay='". $_POST['rdisplay']."', screen_model='".$_POST['screen_model']."', p_hardware='".$_POST['cable']."', pcolor='".$_POST['pcolor']."', pname='".$_POST['pname']."', kboard='".$_POST['kboard']."', ip='".$_POST['ip']."', reg_name='".$_POST['reg_name']."', mem=".$_POST['mem'].", dt_server='".$_POST['dt_server']."', alert='".$_POST['alert']."', ows='".$_POST['ows']."', w_date='".$_POST['w_date']."', flashromver='".$_POST['flashromver']."', s_size='".$_POST['s_size']."', mag='".$_POST['mag']."', rcard='".$_POST['rcard']."', kvsid=".$_POST['kvsid'].", finger='".$_POST['finger']."', stand_alone='".$_POST['stand_alone']."', standards='".$_POST['standards']."', profile='".$_POST['profile']."', man_date='".$_POST['man_date']."', l_sn='".$_POST['l_sn']."', misc='".$_POST['misc']."', problem='".$_POST['problem']."'";

then $query = "insert into $fields";

I receive back

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 ' item='JS900CV', qsver='', flashrom='',expansion='', rdisplay='', screen_model='' at line 1 Blockquote

if I echo the $query I get this:

insert into adv_exchange SET synum='SY5135', worknum='123456', user_id='2', f_name='REMOVED', l_name='REMOVED', email='REMOVED', regnum=, item='JS900CV', qsver='', flashrom='',expansion='', rdisplay='', screen_model='', p_hardware='', pcolor='', pname='', kboard='', ip='192.168.1.16', reg_name='', mem=, dt_server='', alert='', ows='', w_date='', flashromver='', s_size='', mag='', rcard='', kvsid=3, finger='', stand_alone='', standards='', profile='', man_date='', l_sn='', misc='misc test\r\n', problem='gen test'

Depending on what I enter in the error is changing spots in my statement. Not all fields are used the form is dynamic that is supplying the data so the fields are dependent on what options are selected. On a side note in case of concern about using $_POST to insert directly into mysql, I sanitize the array first. Any help would be greatly appreciated.

Olaf Dietsche · Accepted Answer · 2012-12-26 21:21:31Z

4

Look at regnum=,. You don't provide a value for regnum. Either leave it out entirely or set it to an appropriate value.

answered Dec 26, 2012 at 21:21

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

new_to_this Over a year ago

So becuase there is no data in regnum it is giving the error? So I need to buil the insert statment without the empty fields then?

Olaf Dietsche Over a year ago

@new_to_this Yes, either omit the fields or provide a value.

Tom van der Woerdt · Accepted Answer · 2012-12-26 21:23:14Z

3

You're using a very, very bad approach to MySQL databases: manually creating the queries. You should really use prepared statements instead: this issue will be resolved as well.

Don't use mysql_* functions, use PDO instead.

Your code would look like this (simplified):

// This holds the query
$statement = $pdo->prepare('INSERT INTO adv_exchange SET synum=?, worknum=?, etc=?, problem=?');

// This executes it with the given arguments. It's 100% injection-proof and safe. In fact, it's also faster.
$statement->execute(array($synum, $_POST['worknum'], $_POST['therest'], $_POST['problem']));

answered Dec 26, 2012 at 21:23

Tom van der Woerdt

30k7 gold badges76 silver badges105 bronze badges

4 Comments

new_to_this Over a year ago

ok thank you that does look a lot easier to setup and maintain. Being safer is a bonus also thank you for the help.

new_to_this Over a year ago

Would this approach then get around sending empty fields that is causing my problem or would I still need to only send fields that are being used for that submission?

Tom van der Woerdt Over a year ago

This approach will automatically insert empty strings, zeros or NULL values, depending on the POST data. No need to count for missing fields.

new_to_this Over a year ago

Perfect thank you so much. This will help me clean up a lot of my pages.

Bryan Young · Accepted Answer · 2012-12-26 21:24:08Z

0

regnum=".$_POST['regnum']." is causing the problem. When it is undefined, you get regnum=, in the SQL query

A bigger concern is that you are not escaping your inputs. Either use mysql_real_escape_string around them, or better, use prepared statements.

answered Dec 26, 2012 at 21:24

Bryan Young

1217 bronze badges

1 Comment

new_to_this Over a year ago

I have a function I am running against the $_POST array before my query: That runs mysql_real_escape_string, stripes out jave, html, and style tags.

ericwclymer · Accepted Answer · 2012-12-26 21:24:58Z

0

You need to SET regnum=SOMETHING.

Currently it's empty.

answered Dec 26, 2012 at 21:24

ericwclymer

613 bronze badges

Collectives™ on Stack Overflow

I have a SQL Syntax error on my php page

4 Answers 4

2 Comments

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related