MYSQL ERROR

Question

Bear with me I am a bit new to all this,

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, it looks fine so if someone could help me that would be great thanks

Answer 1

You have a single quote missing before $tags.

Answer 2

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.

Answer 3

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

Answer 4

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.