# Populate the database with new tweet data from the json_cache table

I'm really a newbie with code, and I wish to prepare all my statements to avoid SQL injection. I have searched a lot on the internet, but this wrapper/API doesn't look anything like the examples given. And I'm not yet skilled enough to fully understand how I should rework it to fit mine:)

This is my code

<?php
/**
* parse_tweets.php
* Populate the database with new tweet data from the json_cache table
* Latest copy of this code: http://140dev.com/free-twitter-api-source-code-library/
* @author Adam Green <140dev@gmail.com>
* @version BETA 0.30
*/
require_once('140dev_config.php');
require_once('db_lib.php');
$oDB = new db; // This should run continuously as a background process while (true) { // Process all new tweets$query = 'SELECT cache_id, raw_tweet ' .
'FROM json_cache';
$result =$oDB->select($query); while($row = mysqli_fetch_assoc($result)) {$cache_id = $row['cache_id']; // Each JSON payload for a tweet from the API was stored in the database // by serializing it as text and saving it as base64 raw data$tweet_object = unserialize(base64_decode($row['raw_tweet'])); // Delete cached copy of tweet$oDB->select("DELETE FROM json_cache WHERE cache_id = $cache_id"); // Limit tweets tgo a single language, // such as 'en' for English if ($tweet_object->lang <> 'en') {continue;}

// The streaming API sometimes sends duplicates,
// Test the msg_id before inserting
$msg_id =$tweet_object->id_str;
if ($oDB->in_table('tweets','msg_id=' .$msg_id )) {continue;}

// Gather tweet data from the JSON object
// $oDB->escape() escapes ' and " characters, and blocks characters that // could be used in a SQL injection attempt if (isset($tweet_object->retweeted_status)) {
// This is a retweet
// Use the original tweet's entities, they are more complete
$entities =$tweet_object->retweeted_status->entities;
$is_rt = 1; } else {$entities = $tweet_object->entities;$is_rt = 0;
}

$content =$oDB->escape($tweet_object->text);$created_at = $oDB->date($tweet_object->created_at);
if (isset($tweet_object->geo)) {$lat = $tweet_object->geo->coordinates[0];$long = $tweet_object->geo->coordinates[1]; } else {$lat = $long = 0; }$user_object = $tweet_object->user;$user_id = $user_object->id_str;$user_name = $oDB->escape($user_object->screen_name);
$realname =$oDB->escape($user_object->name);$profile_image_url = $user_object->profile_image_url; // Add a new user row or update an existing one$field_values = 'user_name = "' . $user_name . '", ' . 'profile_image_url = "' .$profile_image_url . '", ' .
'user_id = ' . $user_id . ', ' . 'realname = "' .$realname . '", ' .
'location = "' . $oDB->escape($user_object->location) . '", ' .
'url = "' . $user_object->url . '", ' . 'description = "' .$oDB->escape($user_object->description) . '", ' . 'created_at = "' .$oDB->date($user_object->created_at) . '", ' . 'followers_count = ' .$user_object->followers_count . ', ' .
'friends_count = ' . $user_object->friends_count . ', ' . 'statuses_count = ' .$user_object->statuses_count . ', ' .
'time_zone = "' . $user_object->time_zone . '", ' . 'last_update = "' .$oDB->date($tweet_object->created_at) . '"' ; 'type = "' . "twitter" . '"' ; if ($oDB->in_table('users','user_id="' . $user_id . '"')) {$oDB->update('users',$field_values,'user_id = "' .$user_id . '"');
} else {
$oDB->insert('users',$field_values);
}

// Add the new tweet

$field_values = 'msg_id = ' .$msg_id . ', ' .
'content = "' . $content . '", ' . 'created_at = "' .$created_at . '", ' .
'lat = ' . $lat . ', ' . 'long = ' .$long . ', ' .
'user_id = ' . $user_id . ', ' . 'user_name = "' .$user_name . '", ' .
'realname = "' . $realname . '", ' . 'profile_image_url = "' .$profile_image_url . '", ' .
'is_rt = ' . $is_rt;$oDB->insert('tweets',$field_values); // The mentions, tags, and URLs from the entities object are also // parsed into separate tables so they can be data mined later foreach ($entities->user_mentions as $user_mention) {$where = 'msg_id=' . $msg_id . ' ' . 'AND source_user_id=' .$user_id . ' ' .
'AND target_user_id=' . $user_mention->id; if(!$oDB->in_table('tweet_mentions',$where)) {$field_values = 'msg_id=' . $msg_id . ', ' . 'source_user_id=' .$user_id . ', ' .
'target_user_id=' . $user_mention->id;$oDB->insert('tweet_mentions',$field_values); } } foreach ($entities->hashtags as $hashtag) {$where = 'msg_id=' . $msg_id . ' ' . 'AND tag="' .$hashtag->text . '"';

if(! $oDB->in_table('tweet_tags',$where)) {

$field_values = 'msg_id=' .$msg_id . ', ' .
'tag="' . $hashtag->text . '"';$oDB->insert('tweet_tags',$field_values); } } foreach ($entities->urls as $url) { if (empty($url->expanded_url)) {
$url =$url->url;
} else {
$url =$url->expanded_url;
}

$where = 'msg_id=' .$msg_id . ' ' .
'AND url="' . $url . '"'; if(!$oDB->in_table('tweet_urls',$where)) {$field_values = 'msg_id=' . $msg_id . ', ' . 'url="' .$url . '"';

$oDB->insert('tweet_urls',$field_values);
}
}
}

// You can adjust the sleep interval to handle the tweet flow and
// server load you experience
sleep(1);
}

?>

• Is this fully working code as you want it to work? If this is not the case, please ask this on StackOverflow. – Abbas Mar 31 '15 at 7:52
• On stackoverflow they sent me here:( – user3468449 Mar 31 '15 at 8:11
• Nope, I'm not asking that, I'm just asking how I would go over it to do it myself. It's like, I have the house, I have the paint, and I'm just asking advice on how to do so. – user3468449 Mar 31 '15 at 8:33
• I will gladly do it myself because the whole point is that I want to understand it – user3468449 Mar 31 '15 at 8:36
• Well, here's a tutorial: developphp.com/video/PHP/… – KIKO Software Mar 31 '15 at 8:38

I wish to prepare all my statements to avoid SQL injection. but this wrapper/API doesn't look anything like the examples given.

First of all, it's great that you want to do this. Prepared statements are always safer than escaping (and your current script seems to forget to escape some values, which would be a lot easier to catch with prepared statements), and it's actually important even in a script like this, which doesn't accept direct user input, because of second order SQL injection.

You didn't post your db class code, so it's impossibly for us to tell you how to do it with this class. On the other hand, the class doesn't seem to be all that great (you can/must do something like select("DELETE, etc), so you might as well use PDO directly.

The complete intro to PDO can be found here, a shorter one about prepared statements here, and if your problem is how to actually write insert queries, see here. Also note that if you perform the same query multiple times for different values, you only have to prepare once, and can then execute multiple times (which will increase performance). If these links, and the link posted by KIKO in the comments do not help, I would suggest that you try to write prepared statements, and post the non-working code on stackoverflow and ask why it's not working.

Misc

• while (true) { this doesn't seem like a good idea. If for any reason your code throws an error, the loop will stop forever. I would prefer a cron job that runs every x seconds instead.
• your indentation is off quite often, and in very odd places, which makes your code hard to read. Use any IDE to fix this easily.
• you have way too many comments, and very often they don't explain any more than the code already does. Comments inside code are meant to explain why you do something, not what you do (that is what the code does). If you feel that you need to add comments for clarity or to create blocks, then your code should be improved instead. For example, instead of adding Add a new user row or update an existing one, it would be better to extract that code to a addOrUpdateUser(\$basedOnTweet). This is just one example, there are many places where you can extract functions.
• I hope that you store this script outside of the webroot, as otherwise it can very easily be used to DOS your server.
• Thank you tim! These are some solid pointers! I'm just starting out with this stuff, and it's hard! But a lot of fun when you see it working:) And how would it be easy to DOS the server?:) – user3468449 Mar 31 '15 at 12:40
• @user3468449 Yes, at the beginning it's always hard, but once you get it, it gets a lot easier :) regarding DOS: well, if the script is public, say at /var/www/script.php, an attacker can just visit example.com/script.php a couple of (hundred) times. As it performs quite expensive operations (for the database and the cpu), and never ever quits, this would at some point cause the server do slow down considerably (even if there are no new entries, you still have a busy loop, and a couple of database queries). – tim Mar 31 '15 at 12:49