PHP and MYSQL

[Mitchell Loach]

I am trying to make a form post into a table.

However I am only getting "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 'P','4','PD','DIS','Q','Recon','PP','SIV','Retail' )' at line 1"

PHP Code;

<form action="addstockdata.php" method="post">
         <input class="textField" name="regNum" type="text" dir="ltr" value="Reg Number"  onfocus="this.value=''" size="7" maxlength="7" onblur="this.value=this.value.toUpperCase()" />
         <input name="pp" class="textField" type="text" dir="ltr" value="PP"  onfocus="this.value=''" size="7" maxlength="7" />
         <input name="siv" type="text" class="textField" dir="ltr" value="SIV"  onfocus="this.value=''" size="7" maxlength="7" />
         <input name="rp" type="text" dir="ltr" class="textField" value="Retail"  onfocus="this.value=''" size="7" maxlength="7" /> <br />
         <input name="make" type="text" dir="ltr" value="Make" class="textField" onfocus="this.value=''" size="15" maxlength="15" onblur="this.value=this.value.toUpperCase()" />
         <input name="model" type="text" dir="ltr" value="Model" class="textField" onfocus="this.value=''" size="15" maxlength="15" onblur="this.value=this.value.toUpperCase()" />
         <input name="engineSize" type="text" dir="ltr" value="Engine Size" class="textField" onfocus="this.value=''" size="4" maxlength="4" />
         <input name="colour" type="text" dir="ltr" value="Colour"  onfocus="this.value=''" class="textField" size="15" maxlength="15" onblur="this.value=this.value.toUpperCase()"  /> 
         <input name="year" type="text" dir="ltr" value="Year"  onfocus="this.value=''" size="4" maxlength="4" class="textField" />
         <input name="dis" type="text" dir="ltr" value="DIS"  onfocus="this.value=''" size="4" maxlength="4" class="textField" />
         <input name="recon" type="text" dir="ltr" value="Recon"  onfocus="this.value=''" size="6" maxlength="6" class="textField" />
         <input name="pd" type="text" dir="ltr" value="PD"  onfocus="this.value=''" size="6" maxlength="6" class="textField" />
         <select name="doors">
         <option value="2">2 Doors</option>
         <option value="3">3 Doors</option>
         <option value="4">4 Doors</option>
         <option value="5">5 Doors</option>
         <option value="E">Estate</option>
         </select>
         <select name="gearbox">
         <option value="4">4 Gears</option>
         <option value="5">5 Gears</option>
         <option value="6">6 Gears</option>
         <option value="A">Automatic</option>
         <option value="S">Semi-Automatic</option>
         </select>
         <select name="fuel">
         <option value="P">Petrol</option>
         <option value="D">Diesel</option>
         </select>
         <select name="vat">
         <option value="Q">Qualify</option>
         <option value="M">Margin</option>
         </select>
         <input name="spec" type="text" size="20" maxlength="20" value="Spec"  onfocus="this.value=''" />
         <input type="submit" />
         </form>

Post Code;

<?php
 
mysql_connect("localhost:3306","root","") or die ("Unable to connect to MySQL server."); 
$db = mysql_select_db("test") or die ("Unable to select requested database."); 
 $make=$_POST['make']; 
 $model=$_POST['model']; 
 $reg=$_POST['regNum']; 
 $pp=$_POST['pp']; 
 $siv=$_POST['siv']; 
 $cc=$_POST['engineSize']; 
 $colour=$_POST['colour'];  
 $year=$_POST['spec']; 
 $door=$_POST['doors']; 
 $fuel=$_POST['fuel']; 
 $gearbox=$_POST['gearbox']; 
 $pd=$_POST['pd']; 
 $dis=$_POST['dis']; 
 $vat=$_POST['vat']; 
 $recon=$_POST['recon']; 
 $rp=$_POST['rp'];   
 $spec=$_POST['spec'];
 
 mysql_query( "INSERT INTO stock( reg, year, make, model, spec, cc, colour, door, fuel, gearbox, pd, dis, vat, recon, pp, siv, rp) VALUES ('$reg','$year','$make','$model','$spec','$cc','$colour','$door,'$fuel','$gearbox','$pd','$dis','$vat','$recon','$pp','$siv','$rp')"); 
 echo mysql_error();
 mysql_close()
 ?> 

Please Help

Thanks in Advance

[Rabbit]

You're missing a quote after your $door variable.

[Ammu]

mysql_query("INSERT INTO stock(reg,year,make,model,spec,cc,colour,door,fuel,gearbox,pd,dis,vat,recon,pp,siv,rp) VALUES ('$reg','$year','$make','$model','$spec','$cc','$colour','$door','$fuel','$gearbox','$pd','$dis','$vat','$recon','$pp','$siv','$rp')");
 

[stubben]

You should consider to sanitize the input.

I use something like this:

Funktion to clean input:

function form ($data) { // Prevents SQL Injection
   global $db_connect;
   $data = ereg_replace("[\'\")(;|`,<>]", "", $data);
   $data = mysql_real_escape_string(trim($data), $db_connect);
   return stripslashes($data);
}

Handling the input:

$first_name    = form ($_POST['first_name']);
$last_name     = form ($_POST['last_name']);
$title         = form ($_POST['title']);
$email        = form ($_POST['email']);
$phone         = form ($_POST['phone']);
$username     = form ($_POST['username']);
$password     = form ($_POST['password']);

Saving it to the db:

$q =     "INSERT INTO user";
$q .= " SET USER_ID    = ''";    
$q .= ", FIRST_NAME     = '".$first_name."'";    
$q .= ", LAST_NAME     = '".$last_name."'";    
$q .= ", TITLE        = '".$title."'";
$q .= ", EMAIL         = '".$email."'";    
$q .= ", PHONE        = '".$phone."'";    
$q .= ", USER_NAME    = '".$username."'";    
$q .= ", PASSWORD     = '".md5 ($password)."'";    
mysql_query($q);
print mysql_error();

By using this syntax it is a little easyer to exclude empty variables. E.g.:

if (!empty ($phone)) $q .= ", PHONE        = '".$phone."'";

to make the submit as short as possible.

[Dormilich]

Funktion to clean input:

function form ($data) { // Prevents SQL Injection
   global $db_connect;
   $data = ereg_replace("[\'\")(;|`,<>]", "", $data);
   $data = mysql_real_escape_string(trim($data), $db_connect);
   return stripslashes($data);
}

highly susceptible to SQL injection! stripslashes() is just removing the slashes you added in mysql_real_escape_string()!

besides that:
- mysql functions are outdated, use PDO or MySQLi instead
- globals are always a bad idea
- $db_connect ain't used
- ereg functions are deprecated (use preg functions instead)

note: Prepared Statements (via PDO/MySQLi) are absolutely immune to SQL injections