php/mysql : error sending query to mysql database

Question

I have sent queries to mysql databases a lot so it threw me for a loop when this stopped working. I'm just trying to insert text and int and decimal information. Is the syntax wrong somewhere? It throws this error message, but I dont know what it means!

Errormessage:

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 'long) VALUES (jpeg.jpg,1410700,image/jpeg,1372713379,42.978852222222,-86.0356041' at line 1

name, type, datetime table columns are varchar

size is int

lat and long are decimals(14,12) and (15,12)

I have tried many different variations of the query to try and send it to my localhost wampserver. If you see something I missed please let me know. Thanks!

 $conn = mysqli_connect("localhost", "root", "password", "databaseimage") or die('Error Connecting.');
 $sql = "INSERT INTO `images` (name,size,type,datetime,lat,long) VALUES ($imageName,$imageSize,$imageType,$datetime,$lat,$long)";
 if(mysqli_query($conn, $sql)) {
    echo "Added to database successfully!";
 } else {
    echo "Error sending query!";
    printf("\nErrormessage: %s\n", mysqli_error($conn));
 }

I have tried it with and without `` marks. I have tried it with single quotes around each variable. I have tried just sending raw data through and it still errors out.

Ok, try adding single quotes on string variables, like: VALUES ('$imageName',$imageSize,'$imageType',etc..) — Mr.Web
– Mr.Web, Commented Jul 2, 2013 at 23:30
You should really use prepared statements instead of injecting your variables (unquoted...) in your sql statement. — jeroen
– jeroen, Commented Jul 2, 2013 at 23:31

andrewsi · Accepted Answer · 2013-07-02 23:52:30Z

4

LONG is a mySQL reserved word: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

One way to fix it is to wrap those words in backticks:

INSERT INTO `images` (name,size,type,datetime,lat,`long`) VALUES ('$imagename'....

But it's much better practice to avoid using reserved words as column or table names if you can - as you've discovered, it leads to odd situations like this.

edited Jul 2, 2013 at 23:52

answered Jul 2, 2013 at 23:30

andrewsi

10.7k132 gold badges37 silver badges52 bronze badges

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

3 Comments

matt Over a year ago

I did what you said and got this... Errormessage: Unknown column 'jpeg.jpg' in 'field list'

andrewsi Over a year ago

You'll also need to use single quotes around column values - I've updated the answer. You should look at moving to mysqli_*or PDO, though; both will help you write SQL that's a lot more secure.

andrewsi Over a year ago

Ah, mea culpa - yes you are; you're not using prepared statements, though. Putting variables directly into your SQL can be dangerous - if there's an image with a quote in the name, it will break your SQL statement; and there are more malicious things possible than that.

Pradeep Pati · Accepted Answer · 2013-07-03 00:09:21Z

0

The query should look like

$sql = "INSERT INTO `images` (name,size,type,datetime,lat,`long`) VALUES ('$imageName',$imageSize,'$imageType','$datetime',$lat,$long)";

edited Jul 3, 2013 at 0:09

answered Jul 2, 2013 at 23:29

Pradeep Pati

5,9293 gold badges31 silver badges43 bronze badges

Collectives™ on Stack Overflow

php/mysql : error sending query to mysql database

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related