SQL syntax Error when submitting form fields with in the value

Question

I'm having an issue with SQL. I have tried all of the fixes I have found and nothing works.

This is the error I'm getting upon submission of the form:

**Error: 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 'a')' at line 2**

It's referencing the value of the field sent from the text field name="entity_name"

To test my fixes for the other fields I entered text with apostrophes randomly placed in every text field.

Everything works except for the first text field. Whereas before the error would have shown the syntax issue for all text fields and areas.

I have commented out $member = str_replace("'", "'", $member);

and

And have also commented out $member = mysql_real_escape_string($member);

Neither of them fixed the issue but neither is $member = addslashes($member);

Can someone help me figure out why there is still an SQL syntax issue when in reality there shouldn't be? Any help I can get would be greatly appreciated.

Here is my code:

<?php
session_start(); 
$con = mysql_connect("localhost","*******","*********"); 
if (!$con) 
  { 
  die('Could not connect: ' . mysql_error()); 
  } 

mysql_select_db("***********", $con);

$memberlinksafe = $_POST['entity_name'];

function strip_punctuation($memberlinksafe) {
    $memberlinksafe = strtolower($memberlinksafe);
    $memberlinksafe = preg_replace("/[:punct:]+/", "", $memberlinksafe);
    $memberlinksafe = str_replace(" +", "", $memberlinksafe);
    return $memberlinksafe;
} 

//builds data from logo image to store into database
$logofile = $_FILES['cover_photo']['tmp_name'];

$logo = addslashes(file_get_contents($_FILES['cover_photo']['tmp_name']));
$logo_name = addslashes($_FILES['cover_photo']['tmp_name']);

//build data from cover photo image to store into database
$cover_photo_file = $_FILES['cover_photo']['tmp_name'];

$cover_photo = addslashes(file_get_contents($_FILES['cover_photo']['tmp_name']));
$cover_photo_name = addslashes($_FILES['cover_photo']['tmp_name']);

//build data from search photo image to store into database
$search_image_file = $_FILES['cover_photo']['tmp_name'];

$search_image = addslashes(file_get_contents($_FILES['cover_photo']['tmp_name']));
$search_image_name = addslashes($_FILES['cover_photo']['tmp_name']);

$member = $_POST['member'];
$member = addslashes($member);
//$member = str_replace("'", "&#039;", $member);
//$member = mysql_real_escape_string($member);

//$entity_name = $_POST[entity_name];
//$entity_name = addslashes($entity_name);
//$entity_name = str_replace("'", "&#039;", $entity_name);

$keywords = $_POST['keywords'];
$keywords = addslashes($keywords);

$street_address = $_POST['street_address'];
$street_address = addslashes($street_address);

$city = $_POST['city'];
$city = addslashes($city);

$st = $_POST['st'];
$st = addslashes($st);

$mailcode = $_POST['mailcode'];
$mailcode = addslashes($mailcode);

$website = $_POST['website'];
$website = addslashes($website);

$fb_url = $_POST['fb_url'];
$fb_url = addslashes($fb_url);

$hours = $_POST['hours'];
$hours = addslashes($hours);

$ph_number = $_POST['ph_number'];
$ph_number = addslashes($ph_number);

$body_header = $_POST['body_header'];
$body_header = addslashes($body_header);
//$body_header = str_replace("'", "&#039;", $body_header);

$body_text = $_POST['body_text'];
$body_text = str_replace("'", "&#039;", $body_text);

$search_blurb = $_POST['search_blurb'];
$search_blurb = str_replace("'", "&#039;", $search_blurb);

$sql="INSERT INTO *********** (entity_name, category, keywords, street_address, community_id, city, st, country, mailcode, website, fb_url, email, hours, ph_number, body_header, body_text, search_blurb, dd, ad, ed, gd, md, vd, pd, logo, logofilename, cover_photo, coverphotofilename, search_image, searchimagefilename, memberlinksafe) 
VALUES ('$member','$_POST[category]','$keywords','$street_address','$_POST[community_id]','$city','$st','$_POST[country]','$mailcode','$website','$fb_url','$_POST[email]','$hours','$ph_number','$body_header','$body_text','$search_blurb','$_POST[dd]','$_POST[ad]','$_POST[ed]','$_POST[gd]','$_POST[md]','$_POST[vd]','$_POST[pd]','$logo','$logo_name','$cover_photo','$cover_photo_name','$search_image','$search_image_name','$memberlinksafe')"; 

if (!mysql_query($sql,$con)) 
  { 
  die('Error: ' . mysql_error()); 
  } 
echo "<h1>Thank you for submitting your details!</h1><br><p><a href='business-attraction.php'>Add another</a> business/attraction.</p>"; 

mysql_close($con); 

?>

Use PDO or mysqli, specifically, using prepared statements will sort your problem. — hd1, May 29 '13 at 18:35
There are many things wrong here, but first off you should use PDO and prepared statements as the `mysql_*` extensions are deprecated. Many of your escaping and SQL injection vulnerabilities will be fixed just by moving. — doublesharp, May 29 '13 at 18:35
One warning - including $_POST variables directly in your SQL query string allows for SQL injection attacks. — Mike Pelley, May 29 '13 at 18:36
mySQL is deprecated, start using PDO or mySQLi now and parameterized queries to avoid SQL injection and solve this ' problem. See http://stackoverflow.com/questions/10703426/php-pdo-and-mysqli for more info — xQbert, May 29 '13 at 18:37
Will you show the insert query after the variable interpolation? — George Cummins, May 29 '13 at 18:49
the really query we need to look you didnt show it . this error is for the query . — echo_Me, May 29 '13 at 18:54
Is there not a scroll bar in the code block? I posted the INSERT query. — Jeff Peak, May 29 '13 at 19:05

score 0 · Accepted Answer · answered May 29 '13 at 19:02

0

first i could see in your query those are wrong , you need quotes inside POST , and you need to escape them also.

like that

 $dd = mysql_real_escape_string($_POST["dd"]) ;

change this

'$_POST[dd]','$_POST[ad]','$_POST[ed]','$_POST[gd]','$_POST[md]','$_POST[vd]','$_POST[pd]'

to

'$dd','$_POST["ad"]','$_POST["ed"]','$_POST["gd"]','$_POST["md"]','$_POST["vd"]','$_POST["pd"]',
 ^^---//continue with other variables escaped like this one

answered May 29 '13 at 19:02

echo_Me

37,078
5
58
78

echo_Samir - I've made this change as you've requested and the error is still occuring, but still only with the first variable. – Jeff Peak May 29 '13 at 21:04
what first variable ? and please submit also what error you are getting. – echo_Me May 29 '13 at 21:14
$member This is the error that shows in the browser after the form is submitted. Error: 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 'a')' at line 1 – Jeff Peak May 29 '13 at 21:38
what is a ? is the member called a ? or something else called a . – echo_Me May 29 '13 at 21:52
a is the character the comes after the apostrophe that was submitted in the text field that becomes the variable $member from $_POST["member"] – Jeff Peak May 29 '13 at 21:55
The text field with where name="member" is submitted with this value, Test'a – Jeff Peak May 29 '13 at 21:55
Upon submission I get the mentioned error in the browser. – Jeff Peak May 29 '13 at 21:56
then your error is here `Test'a` you must escape this apostrofe , try do the member variable like that `'".$member."'` inside the query – echo_Me May 29 '13 at 21:59
Just tried this, still get the error. I really don't understand what is going on. – Jeff Peak May 29 '13 at 22:24
remove `Test'a` and replace it by `Test-a` it will work – echo_Me May 29 '13 at 22:27
echo_Samir the issue is, I can use an apostrophe in every other field except the first field, it enters the database just fine as either an escaped string or replaced string. The site that this is for requires that users are able to use apostrophes where necessary. The first field is a name filed and telling a user that they have to enter "Sally-s Beauty Shop" instead of "Sally's Beauty Shop" isn't something I can do. – Jeff Peak May 29 '13 at 22:40

SQL syntax Error when submitting form fields with in the value

1 Answers1