Populate the database with new tweet data from the json_cache table

Question

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 <[email protected]>
* @license GNU Public License
* @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);
}

?>

Answer 1

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.