Syntax Error:Check the manual that corresponds to your MySQL server version for the right syntax to use near ''sfapp' ('surname_add','forename_add')

[shughes19]

Hi guys

 

I have this code below and all works fine when submitting this online application apart from when someone types either ' # & into one of the comment fields in which it throws up the error. Have tried various fixes from across the internet but no joy. Can anyone offer suggestions?

 

<?php

$con = mysql_connect("localhost:3306","root","password");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db('sfapp', $con);

$sql="INSERT INTO 'sfapp' ('surname_add','forename_add','dob_add','hometele_add','mobiletele_add','homeadd_add','siblings_add','schoolname_add','headname_add','schooladd_add','schooltele_add','schoolem_add','alevel_add','personstate_add','nameprovided_add','pe_add','se_add','PredGrade_Art','PredGrade_AScience','PredGrade_BusStudies','PredGrade_Electronics','PredGrade_EnglishLang','PredGrade_EnglishLit','PredGrade_French','PredGrade_German','PredGrade_Geog','PredGrade_Graphics','PredGrade_History','PredGrade_Maths','PredGrade_SepScience','PredGrade_ProductDesign','PredGrade_Spanish','PredGrade_Other','Gender_Male','Gender_Female','Sub_EnglishLit','Sub_Maths','Sub_FurtherMaths','Sub_Biology','Sub_Chemistry','Sub_Physics','Sub_French','Sub_German','Sub_Spanish','Sub_Geography','Sub_History','Sub_RE','Sub_FineArt','Sub_Business','Sub_Computing','Sub_GlobPersp','Sub_DramaAndTheatre','Sub_PE','Sub_Dance','Sub_Politics','Sub_Psychology','Sub_Sociology','readprospect_chk','Sib_Yes','Sib_No','Current_Student_Yes','Current_Student_No','I_Understand_chk','Current_Education_chk','Local_Care_chk','Staff_Cwhls_chk','Sub_Film')

 

VALUES

('$_POST[surname_add]','$_POST[forename_add]','$_POST[dob_add]','$_POST[hometele_add]','$_POST[mobiletele_add]','$_POST[homeadd_add]','$_POST[siblings_add]','$_POST[schoolname_add]','$_POST[headname_add]','$_POST[schooladd_add]','$_POST[schooltele_add]','$_POST[schoolem_add]','$_POST[alevel_add]','$_POST[personstate_add]','$_POST[nameprovided_add]','$_POST[pe_add]','$_POST[se_add]','$_POST[PredGrade_Art]','$_POST[PredGrade_AScience]','$_POST[PredGrade_BusStudies]','$_POST[PredGrade_Electronics]','$_POST[PredGrade_EnglishLang]','$_POST[PredGrade_EnglishLit]','$_POST[PredGrade_French]','$_POST[PredGrade_German]','$_POST[PredGrade_Geog]','$_POST[PredGrade_Graphics]','$_POST[PredGrade_History]','$_POST[PredGrade_Maths]','$_POST[PredGrade_SepScience]','$_POST[PredGrade_ProductDesign]','$_POST[PredGrade_Spanish]','$_POST[PredGrade_Other]','$_POST[Gender_Male]','$_POST[Gender_Female]','$_POST[sub_EnglishLit]','$_POST[sub_Maths]','$_POST[sub_FurtherMaths]','$_POST[sub_Biology]','$_POST[sub_Chemistry]','$_POST[sub_Physics]','$_POST[sub_French]','$_POST[sub_German]','$_POST[sub_Spanish]','$_POST[sub_Geography]','$_POST[sub_History]','$_POST[sub_RE]','$_POST[sub_FineArt]','$_POST[sub_Business]','$_POST[sub_Computing]','$_POST[sub_GlobPersp]','$_POST[sub_DramaAndTheatre]','$_POST[sub_PE]','$_POST[sub_Dance]','$_POST[sub_Politics]','$_POST[sub_Psychology]','$_POST[sub_Sociology]','$_POST[readprospect_chk]','$_POST[sib_Yes]','$_POST[sib_No]','$_POST[Current_Student_Yes]','$_POST[Current_Student_No]','$_POST[i_Understand_chk]','$_POST[Current_Education_chk]','$_POST[Local_Care_chk]','$_POST[staff_Cwhls_chk]','$_POST[sub_Film]')";

if (!mysql_query($sql,$con))

  {

  die('Error: ' . mysql_error());

  }

 

?>

<?php

//if "email" variable is filled out, send email

  if (isset($_REQUEST['pe_add']))  {

 

  //Email information

 

  $admin_email = $_REQUEST['pe_add'];

  $forename = $_REQUEST['forename_add'];

  $email = "autoreply@testing.com";

  $subject = "Application";

  $desc =

  "Dear $forename

  Thank you for submitting your online application, we will be in touch shortly.

"

;

 

 

  //send email

  mail($admin_email, "$subject", "$desc", "From:" . $email);

 

  //Email response

  echo "Thank you for contacting us!";

  }

 

  //if "email" variable is not filled out, display the form

  else  {

?>

If you are seeing this, you need to go back and fill out the Personal Email section!

<?php

  }

  header("location:complete.php");

mysql_close($con)

?>

 

 

Thanks in advance.

[mac_gyver]

string data values that are put into an sql query statement must be escaped, using your database library's string escape function (or use prepared queries) and numerical data values that are put into an sql query statement must be filtered/validated/cast as the appropriate numerical data type (or use prepared queries) in order to prevent sql errors and to prevent sql injection.

 

btw - if you have that many fields being put into a query, you should be dynamically building the query using code, rather than typing out the whole query statement. this would also make it easier for your code to escape/filter/validate/cast values (or use prepared queries) since you would be looping over the fields/data values, rather than have each one hard-coded and individually written out.

[Barand]

You should be normalizing your data and not storing data in db tables as you would in a spreadsheet.

[maxxd]

Is 'sfapp' the name of the database or the name of the table? You select 'sfapp' as the database, then attempt to enter data into 'sfapp' on the next line.

 

*edit* Sorry, misread the issue - mac_gyver is correct. You need to escape and sanitize your data before entering it into the database, and prepared queries are your best bet. Which you'll be able to use if you stop using the mysql_* functions (which have been deprecated for about a decade now) and move to PDO or Mysqli classes.

Edited November 29, 2014 by maxxd