MYSQL_INSERT not working

Question

I have tried multiple times to insert into a database. The values contain a single quote - magic quotes are turned off, addslashes() and mysql_real_escape_string() both escape the characters but the script dies without adding to the database. I have also manually escaped but this failed as well. However, even removing the apostrophe, the script still dies.

The error is: Could not insert staff: 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 '11, Hazel, Blonde, Has never missed a day of work, Graduated from Berkley, Serve' at line 2 Anyone see any issues?

<?php
include('header.php');

$amount = 1;
$staffnum = '0101';
$height = array("5'11", "5'4", "6'2","5'5", "6'4");
$eye = array("Blue","Green","Hazel","Brown");
$hair = array("Brown", "Black", "Blonde", "Red");
$about1 = "Has never missed a day of work";
$about2 = "Graduated from Berkley";
$positions =  array('Server, Bartender', 'Bartender, Host', 'Sever, Host, Bartender', 'Cocktail Server, Bartender, Server'); 
$img = "none";
// arrays
$times = 1;


while($times <= 50) {
$staffnum ++; 
$heighta = mysql_real_escape_string($height[array_rand($height)]);
$eyea =  mysql_real_escape_string($eye[array_rand($eye)]);
$haira =  mysql_real_escape_string($hair[array_rand($hair)]);
$positionsa =   mysql_real_escape_string($positions[array_rand($positions)]);
$about1 =  mysql_real_escape_string($about1);
$about2 =   mysql_real_escape_string($about2);
$img =  mysql_real_escape_string($img);
$staffnum =  mysql_real_escape_string($staffnum);

$insert_staff = "INSERT INTO staff (staffnum, img_link, height, eye, hair, abt1, abt2, titles)
VALUES ($staffnum, $img, $heighta, $eyea, $haira, $about1, $about2, $positionsa)";

$insert_query = mysql_query($insert_staff);

if($insert_query) {
    ?>

<center>
  Member # <?php echo $staffnum; ?> has been added to the database.<br />
  <?php
} else {

  die('Could not insert staff: ' . mysql_error());

}

$times ++;
}

include('footer.php');
?>
  <a href="staff_insert.php?page=1">Return To Staff Insert</a>
</center>

You'll need to quote your non-numeric fields. But you should definitely look at using PDO or mysqli - they'll both help you write code that's a lot more secure. — andrewsi
– andrewsi, Commented Nov 21, 2012 at 21:44
Stay away from mysql_query, please. Don't write code using this dangerous, deprecated interface. PDO takes all of thirty minutes to pick up and is considerably easier and safer to use. — tadman
– tadman, Commented Nov 22, 2012 at 0:56

user428517 · Accepted Answer · 2012-11-21 21:44:21Z

2

You need to put quotes around the string variables you're inserting:

$insert_staff = "INSERT INTO staff (staffnum, img_link, height, eye, hair, abt1, abt2, titles)
VALUES ('$staffnum', '$img', '$heighta', '$eyea', '$haira', '$about1', '$about2', '$positionsa')";

answered Nov 21, 2012 at 21:44

user428517

4,2031 gold badge24 silver badges39 bronze badges

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

1 Comment

Elec Boothe Over a year ago

Rookie mistake :) Thanks a lot!

shad · Accepted Answer · 2012-11-21 21:47:29Z

-1

It's a little bit complicated when you want to send so many variables with basic mysql_query. You should try PDO or mysqli but if you need to use your code, it should be more like

$insert_staff = "INSERT INTO staff (staffnum, img_link, height, eye, hair, abt1, abt2, titles)
VALUES ('".$staffnum."', '".$img."', '".$heighta."', '".$eyea."', '".$haira."', '".$about1."', '".$about2."', '".$positionsa."')";

answered Nov 21, 2012 at 21:47

shad

3711 gold badge2 silver badges8 bronze badges

2 Comments

user428517 Over a year ago

This will work, but Elec is already using mysql_real_escape_string() on all the values he's going to insert, so there's no need to use double quotes to protect strings containing single quotes. That said, of course PDO or mysqli would be a better approach altogether.

tadman Over a year ago

If you're just doing string concatenation, let the double quotes do their thing for you. This is an anti-pattern that needs to die.

Collectives™ on Stack Overflow

MYSQL_INSERT not working

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related