Errors inserting strings into Mysql temporary table

Question

I'm sure this is a simple solution but so far I'm stumped...

So I'm attempting to insert data into a temporary mysql table using the code below:

mysql_query("CREATE TEMPORARY TABLE IF NOT EXISTS data(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),

name VARCHAR(255))")
or die(mysql_error());

$search_term = 'snakes';
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'http://gdata.youtube.com/feeds    
/api/videos?q='.$search_term.'&safeSearch=none&orderby=viewCount&v=2&alt=json&start- 
index=1&max-results=20');
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 10);
$output = curl_exec($ch);
curl_close($ch);

$data = json_decode($output,true); 

$info = $data["feed"];
$video = $info["entry"];
$nVideo = count($video);


foreach ($video as $video) {

$video_id = $video['media$group']['yt$videoid']['$t'];
$title = $video['title']['$t'];

$insert = "INSERT INTO data (name)  " .

      "VALUES
       ('$video_id')";             

$results = mysql_query($insert)
or die(mysql_error()); 

}

When I leave this code as is (with $video_id as the value that I'm inserting into the table) everything works perfectly. However if I change $video_id and attempt to insert $title instead like this:

foreach ($video as $video) {

$video_id = $video['media$group']['yt$videoid']['$t'];
$title = $video['title']['$t'];
echo $title;
$insert = "INSERT INTO data (name)  " .

      "VALUES
       ('$title')";             

$results = mysql_query($insert)
or die(mysql_error()); 

}

I get "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 'm a Snake ( original / official ) [ As seen on Tosh.0 ]')' at line 2". Now when I echo out the values for title within my foreach loop like this....

foreach ($video as $video) {

$video_id = $video['media$group']['yt$videoid']['$t'];
$title = $video['title']['$t'];
echo $title;
echo "<br>";
$insert = "INSERT INTO data (name)  " .

      "VALUES
       ('$video_id')";             

$results = mysql_query($insert)
or die(mysql_error()); 

}

I get:

GIANT SNAKE EATS SECURITY GUARD
Snake woman
Biggest Snake of the World for Sale, 25 000 €
Giant Snake
A Huge Centipede Fighting A Snake
Scary Killer Snakes, World Biggest Snake Ever!
Five Headed Snake In India.
Clelia eats Bothrops
snake swallowed a hippo
The world biggest Snake has been found in SAAD - Karaj (Iran)
WORLD LARGEST SNAKE FOUND DEAD! ★★★★★
Cobra vs. Rat Snake
I'm a Snake ( original / official ) [ As seen on Tosh.0 ]
Bull Snake Against Squirrel
The Biggest Snake In The World
Shark Vs. Sea Snake
Killer Karaoke - Karaoke Singer Gets Dunked in a Tank of Snakes
This is What Snake Venom Does to Blood!
Snake befriends its hamster lunch in zoo
ZOMBIE SNAKE HEAD STILL ALIVE!

I know these values are strings with some odd characters so my question is... is my syntax really wrong like the error message implies or do I need to use a different data type other than VARCHAR?

Thanks!

Answer 1

To give a bit more backstory to Barmar's great answer:

You never want to insert a string directly into a query in the way you are doing. Remember, the database that is ultimatly running your query gets the fully resolved string. This means it doesn't know what is a variable and what was manually typed.

For instance:

$var = "that's not good";
$query = "SELECT * from phrases where phrases.content = '$var'"
echo $query;

would output:

SELECT * from phrases where phrases.content = 'that's not good'

Notice how the string ends after "that" and the resulting query is not proper syntax.

I'm guessing this is what's going on in your situation, since titles are prone to having quotes in them.

What mysql_real_escape_string does is it scans strings for special characters and "escapes them" (generally by adding a backslash) so that there is no way it would accidentally close out the query you're writing, keeping it safely contained in the quotes.

---

All of that explanation above is really important in general when you are writing code to insert into databases. Not sanatizing your strings properly is a major security hole, and leads to a type of attack called an "SQL Injection." What if one of your titles was something like "'; drop table users;"

The plus side is now you can understand this XKCD comic.

Answer 2

Try:

$title = mysql_real_escape_string($video['title']['$t']);

to ensure that special characters in the title are properly escaped.

However, it would be better to convert to mysqli or PDO and use prepared statements, rather than interpolating strings into queries. The mysql extension is deprecated and unmaintained.