CODEWITHSUNDEEP
phpmysql
user81954
user81954

Reputation:

MYSQL ERROR: "You have an error in your SQL syntax"

I have this query running in my PHP script:

$insertQuery = "INSERT INTO blog_articles 
     VALUES '$title', $tags', '$category', '$blog', '$author', '$date'";

I then run this script:

    if ($result = $connector->query($insertQuery)){
    // It worked, give confirmation
    echo '<center><b>Article added to the database</b></center><br>';
}else{
    // It hasn't worked so stop. Better error handling code would be good here!
    die (mysql_error());
}
}

I get this 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 'Title Number 1, General, Blogging, Kayaking, General, Tgis is blog number spelli' at line 2

But I cannot tell what the error is.

Upvotes: 1

Views: 225

Answers (4)

Alistair Knock
Alistair Knock

Reputation: 1836

You have a single quote missing before $tags.

Upvotes: 6

&#211;lafur Waage
&#211;lafur Waage

Reputation: 70021

Your query should be more like this

INSERT INTO blog_articles (`title`, `tags`, `category`, `blog`, `author`, `date`)
VALUES ('$title', '$tags', '$category', '$blog', '$author', '$date')

You should also look into sanitizing your query. Perhaps this way (but i don't know your exact setup, so results might vary)

$sql = sprintf("INSERT INTO blog_articles (`title`, `tags`, `category`, 
    `blog`, `author`, `date`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
mysql_real_escape_string($title), mysql_real_escape_string($tags), 
mysql_real_escape_string($category),  mysql_real_escape_string($blog), 
mysql_real_escape_string($author),  mysql_real_escape_string($date));

This uses the sprintf() function, the php documentation has some great examples.

Upvotes: 4

Chris AtLee
Chris AtLee

Reputation: 8076

As aknock says, you are missing a ' before $tags.

However, you really need to be using mysql_escape_string to protect against SQL injection attacks. Using mysql_escape_string for your SQL query parameters is a good habit to get into.

Using a DB wrapper like PEAR can make escaping parameters much less painful. Your code above could be written like:

$insertQuery = "INSERT INTO blog_articles \
                  (`title`, `tags`, `category`, `blog`, `author`, `date`) \
                  VALUES (?, ?, ?, ?, ?, ?)";

$data = array($title, $tags, $category, $blog, $author, $date);

if ($result = $connector->query($insertQuery, $data)) {
    // It worked, give confirmation
    echo '<center><b>Article added to the database</b></center><br>';
}else{
    // It hasn't worked so stop. Better error handling code would be good here!
    die (mysql_error());
}

(assuming $connector is a PEAR DB object)

Explicitly giving the names and order of the columns that you're inserting makes your code much more maintainable and readable. If you change the database schema later, you will be protected from inserting values into the wrong column, or into columns that don't exist any more.

Upvotes: 0

Benjamin Ortuzar
Benjamin Ortuzar

Reputation: 7831

You need to add the names of the fields you are inserting to

INSERT INTO blog_articles ('title', 'tags', 'category', 'blog', 'author', 'date') VALUES ('$title', '$tags', '$category', '$blog', '$author', '$date')

Also you should add some code to escape double or single quote in your text that could break the SQL query.

use the PHP function mysql_real_escape_string()

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

For more details: http://uk.php.net/mysql_real_escape_string

Upvotes: 0

Related Questions