Is it a bad practice to go directly to the mysql database while developing a plugin?

Question

This is a sample of the Vote it Up plugin:

//Run this to create an entry for a post in the voting system. Will check if the post exists. If it doesn't, it will create an entry.
function SetPost($post_ID) {
    global $wpdb;

    //prevents SQL injection
    $p_ID = $wpdb->escape($post_ID);

    //Check if entry exists
    $id_raw = $wpdb->get_var("SELECT ID FROM ".$wpdb->prefix."votes WHERE post='".$p_ID."'");
    if ($id_raw != '') {
        //entry exists, do nothing
    } else {
        //entry does not exist
        $wpdb->query("INSERT INTO ".$wpdb->prefix."votes (post, votes, guests, usersinks, guestsinks) VALUES(".$p_ID.", '', '', '', '') ") or die(mysql_error());
    }
}

//Run this to create an entry for a user in the voting system. Will check if the user exists. If it doesn't, it will create an entry.
function SetUser($user_ID) {
    global $wpdb;

    //prevents SQL injection
    $u_ID = $wpdb->escape($user_ID);

    //Check if entry exists
    $id_raw = $wpdb->get_var("SELECT ID FROM ".$wpdb->prefix."votes_users WHERE user='".$u_ID."'");
    if ($id_raw != '') {
        //entry exists, do nothing
    } else {
        //entry does not exist
        $wpdb->query("INSERT INTO ".$wpdb->prefix."votes_users (user, votes, sinks) VALUES(".$u_ID.", '', '') ") or die(mysql_error());
    }
}

//Returns the vote count
function GetVotes($post_ID, $percent = false) {
    global $wpdb;

    //prevents SQL injection
    $p_ID = $wpdb->escape($post_ID);

    //Create entries if not existant
    SetPost($p_ID);

    //Gets the votes
    $votes_raw = $wpdb->get_var("SELECT votes FROM  ".$wpdb->prefix."votes WHERE post='".$p_ID."'");
    $sinks_raw = $wpdb->get_var("SELECT usersinks FROM  ".$wpdb->prefix."votes WHERE post='".$p_ID."'");
    $guestvotes_raw = $wpdb->get_var("SELECT guests FROM  ".$wpdb->prefix."votes WHERE post='".$p_ID."'");
    $guestsinks_raw = $wpdb->get_var("SELECT guestsinks FROM  ".$wpdb->prefix."votes WHERE post='".$p_ID."'");
/* Deprecated
    $uservotes_raw = $wpdb->get_var("SELECT votes FROM ".$wpdb->prefix."votes_users WHERE user='".$u_ID."'");
    $usersinks_raw = $wpdb->get_var("SELECT sinks FROM ".$wpdb->prefix."votes_users WHERE user='".$u_ID."'");
*/

    //Put it in array form
    $votes = explode(",", $votes_raw);
    $sinks = explode(",", $sinks_raw);
    $guestvotes = explode(",", $guestvotes_raw);
    $guestsinks = explode(",", $guestsinks_raw);
/* Deprecated
    $uservotes = explode(",", $uservotes_raw);
    $usersinks = explode(",", $usersinks_raw);
*/
    $uservotes = 0;
    $usersinks = 0;

    $initial = 0; //Initial no. of votes [will be placed at -1 when all posts receive votes]

(and so on...)

I've heard from many people that it is a bad practice to go directly to the mysql database. That the code can break up in future versions of Wordpress.

Or it is necessary in some plugins like Vote it Up?

Answer 1

Hi @janoChen:

The correct answer is: "It depends."

In general it is better to use built-in WordPress functions than to use direct SQL when there are built-in functions that can provide you what you need. However, the answers to many of the questions I see here on WPSE are (unfortunately) "you need to use direct SQL for that because WordPress simply doesn't provide a(n efficient) function to provide what you need."

In the case of the Vote It Up plugin it is arguable that their choice of direct SQL was correct because there is not an efficient place to store votes in the WordPress database. On the other hand there are those who would argue that they should have used wp_postmeta and wp_usermeta to store that information. Personally I'm on the fence for a voting plugin; I'd actually have to implement one (which I have done but it was almost 2 years ago and have learned much since) to really have an opinion on the best way of doing it.

One thing I will say is that, almost whenever possible I prefer to use the built-in tables with WordPress rather than to add new tables. Adding a table has a more significant impact than adding SQL code to access the existing tables. Given that alone I might learn towards using the meta tables for a voting plugin but I honestly I can't state that unequivocally unless and until I actually tried to implement such functionality.

UPDATE

I just read the code by c.bavota and in some ways it is good, in other ways it troubles me. I like that he is using post meta, but I don't like that he is putting the list of comma-separated user_ids into a post meta field. What happens when a post gets 25,000 votes? Clearly his code does not scale for a high-traffic site. And his code would make it very slow to get a list of all posts voted on by a given user for any site with a large number of posts.

If he is going to stuff all user IDs for votes into a meta field he should do in a more scalable way such as storing post IDs in user meta as chances of one user voting 25,000 times, or even 2500 times is pretty small. Or he could store votes by embedding the user ID in the post meta key, i.e. "user_vote_{$user_id}" (though maybe 25,000 meta records would cause problems of its own.)

OTOH, if you really need to track votes by user that's where I might argue for a table and direct SQL might make sense.

What's more he rolls his own web service instead of using the build-in AJAX functionality for web services. While I don't like that the built-in functionality is not RESTful, I don't think it is a good idea to recreate a web service infrastructure unless you do it right. Doing it right would include built-in security and c.bavota's code doesn't worry about security; no escaping of $_POST values, no using nonces, nothing.

And his code could easily under-count votes it two or more people are voting at the same time. What's a bit scary is this guy is selling premium themes and his how-to articles have code that violates several known best practices. But I think he is hardly unique as a theme vendor that has code with security or scalability issues. <sigh>.

I left a comment about these issues and suggesting he warn his users immediately and update with better techniques, but it is currently in moderation. Let's hope he publishes it.

UPDATE2

Why use functions like update_post_meta() rather than updating the database directly? Several reasons, but much more important for plugins or themes you plan to distribute than for code you write for your own site (though ideally it's helpful for the latter too.):

1. By some small chance WordPress may chance the database structure for meta. They've done it before and might do it again. If you use the built-in functions instead of SQL your code will continue to work but obviously direct SQL code will break on a WordPress upgrade.

2. If you use update_post_meta() it will work for single site and Multisite. Direct SQL code will work for one but not the other.

3. If you use update_post_meta() and someone wants to use a plugin that stores frequently accessed values in MemCached your code will support it, but not if you write direct SQL.

4. In general if a plugin or theme hooks update_post_meta() the hook will work if you use update_post_meta() but not if you use direct SQL.

5. And I'm sure there are other reasons I can't think of right now.

Suffice it to say that using the built-in functions offers robustness and flexibility that direct SQL can't match; so only use direct SQL when you can't do it with built-in functions. JMTCW.

Answer 2

For most plugins it is sufficient to use the WordPress Options-API. While this works fine for buttons, strings and other simple things, more complex plugins sometimes need their own database table. If you are in such a situation you should follow the official guidelines at the page Creating Tables with Plugins.

There is nothing wrong with going directly into the database as long as you verify the input properly and make sure that your code is absolutely secure. WordPress has a lot of functions that make this a lot easier (again, see: Creating Tables with Plugins), which is why I personally recommend that way.