error when inserting data into a database

amarafzal

Hi All

Got a problem where when i try to insert data in to a database which is coming from a form, if there is an apostrephe (i.e words like you'll etc). I get an SQL error like:

You have an error in your SQL syntax near 'sfs.sfj,as f:asfdsa-sadf ', 'London', 'tes', 'not stated', 'tes', 'test')' at line 1

My php code that im using is:
<?
$db_name = "w";
$table_name = "js";
$connection = @mysql_connect("78979", "iiii", "jjjj") or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());
//check for duplicate ref numbers
$sql = "SELECT job_id from $table_name where job_id='$_POST[job_id]'";
$result = @($sql,$connection) or die(mysql_error());

if(@mysql_num_rows($result)>0){
//inform user the job id is already taken
echo "The JOB REFERENCE NUMBER you added is already taken. Please click on the link below to go back and choose a unique field (i.e use letters aswell as numbers).";
echo "<a href='#' onclick='history.back();'><br><br>Go back</a>  ";
echo "<a href=jobs_add2.htm><br><br>Add another job</a>";
}else{
$sql = "INSERT INTO $table_name (job_id, contact_name, contact_phone, email, date_posted, job_sector, job_title, job_summary, region, location, job_status, salary, career_level) VALUES ('$POST[job_id]', '$POST[contact_name]', '$POST[contact_phone]', '$POST[email]', '$POST[date_posted]', '$POST[job_sector]','$POST[job_title]', '$POST[job_summary]', '$POST[region]', '$POST[location]', '$POST[job_status]', '$POST[salary]', '$_POST[career_level]')";
echo "Thank you. Your job $job_title has been posted on the talent website.";
$result = @($sql,$connection) or die(mysql_error());
}
?>

Any ideas as this is causing problems with my website. PLEASE!!!

phillip_s

You should use addslashes() on any string you insert into the database. When retrieving informartion from db you should use stripslashes(). Look also in the manual.

buck2bcr

Originally posted by amarafzal
Got a problem where when i try to insert data in to a database which is coming from a form, if there is an apostrephe (i.e words like you'll etc). I get an SQL error like:

You have an error in your SQL syntax near 'sfs.sfj,as f:asfdsa-sadf ', 'London', 'tes', 'not stated', 'tes', 'test')' at line 1

You may want to use addslashes($variable); in your code.

The addslashes function returns the text argument with backslashes preceding characters that havespecial meaning in databalse queryies. These are single quotes ('), double quotes ("), and backslashes themselves ().

A releated function to that is the stripslashes($variable), which as you could guess returns the text argument with the backslash encoding removed.

HTH

Buck

buck2bcr

Darn it. Someone slipped in a reply while I was typing. Oh well, what he said. 🙂

Buck

amarafzal

i tried the following, but that didnt work, what am i doing wrong?

<?
$db_name = "w";
$table_name = "js";
$connection = @mysql_connect("2", "df", "df") or die(mysql_error());
$db = @mysql_select_db($db_name, $connection) or die(mysql_error());
//check for duplicate ref numbers
$sql = "SELECT job_id from $table_name where job_id='$_POST[job_id]'";
$result = @($sql,$connection) or die(mysql_error());

$contact_name = addslashes($row['contact_name']);
$contact_phone = $row['contact_phone'];
$date_posted = $row['date_posted'];
$job_title = addslashes($row['job_title']);
$job_summary = addslashes($row['job_summary']);
$location = addslashes($row['location']);
$job_status = addslashes($row['job_status']);
$salary = addslashes($row['salary'];
$career_level = addslashes($row['career_level']);

amarafzal

also tried this, no luck. help

$contact_name = addslashes($contact_name);
$contact_phone = addslashes($contact_phone);
$date_posted = addslashes($date_posted);
$job_title = addslashes($job_title);
$job_summary = addslashes($job_summary);
$location = addslashes($location);
$job_status = addslashes($job_status);
$salary = addslashes($salary);
$career_level = addslashes($career_level);

buck2bcr

I see when you used the addslashes you put the variables into new variable names. Are you using the new variables in your query?

Buck