Multidimension array causing mysql error in PHP Insert query

Question

mysql_query("INSERT INTO data(timestamp,type,lift,sets,reps,weight) VALUES(".$time.",".$_POST[type][$i].",".$_POST[lift][$i].",".$_POST[sets][$i].",".$_POST[reps][$i].",".$_POST[weight][$i].")")or die(mysql_error());

My code is as above. All the variables are set.

The error I am getting is

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 'Standing DB Hammer Curls,1,2,3)' at line 1

There is seemingly no reason for this.

Could someone clarifty on the problem, and ideally explain why multi dimensional arrays are so particular as far as what they need to be enclosed between etc.

Cheers

Jake N · Accepted Answer · 2011-04-09 20:58:55Z

1

Are you adding ' single quotes around the variables your inserting?

This will fail

$t = "Test";
mysql_query("INSERT INTO data(mystring) VALUES (" . $t . ")");

This will work

$t = "Test";
mysql_query("INSERT INTO data(mystring) VALUES ('" . $t . "')");

Remember to defend against MySql Injection

Edit: Thanks Eric

Yes you should use something like mysql_real_escape_string() to escape the single quotes that might be in your POSTd variables.

Edit: About Injection

Briefly....because you need to wrap strings in ' (single quotes) if a ' is typed into a form and then submitted then it can break the insert or update code.

If the following is entered into a form "O'Brien" then the MySql code would be

INSERT INTO data(mystring) VALUES ('O'Brien')

Which is of course incorrect, such things can be exploited and used to gain entry to a web site. This was exploited world wide recently

edited Apr 9, 2011 at 20:58

answered Apr 9, 2011 at 20:35

Jake N

10.6k12 gold badges69 silver badges114 bronze badges

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

4 Comments

Eric Over a year ago

To add to Jake's reply, to defend against MySQL Injection, you'll want to escape any quotes in the data you're inserting.

Thomas Clowes Over a year ago

Many Thanks. Would greatly appreciate it if you could clarify as to why exactly such is needed? Cheers

Jake N Over a year ago

Thomas, I have edited with some more info, Google it though, there is a lot out there.

Eric Over a year ago

My pleasure. Good answer to the OP.

Collectives™ on Stack Overflow

Multidimension array causing mysql error in PHP Insert query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related