This is a story about WordPress 3, that had me head scratching till a little after 3 [am that is, EST Time...] ( sing that to the theme of the Beverly Hillbillies, it’s fun, I promise!) I’ll be hearing banjos playing all night now!

Ok so what inspired that moment of madness, well I’ve been pluging away writing WP Plugins like a mad thing and going great guns till I decided I had best stick to the rules and use the $wpdb->prepare Statement. After all it takes care of all the escaping and htmlspecialchars ( I think ) which keeps my code nice and clean and gives me a warm fuzzy feeling my SQL is a little more protected. ( You do , do those things don’t you?) MySQL Injections are much nastier than the real kind of injections.

Traps for Old Players.

So the database stuff was working like a bought one, using a looong sprintf affair using ( the field names have been changed and reduced in number to protect the innocent!)

$sql = sprintf(“INSERT INTO %s (id,name,event) values (%d,’%s’,'%s’) ON DUPLICATE KEY UPDATE name = %s, event =%s”,

$table_name,

$row['id'],$row['name'],$row['event'],

$row['name'],$row['event']);

The ID is defined as UNIQUE so if I try to insert the same ID, it will perform an Update Instead. A really nice feature , Well I think it is! Don’t you just love Databases?

And if you squirt that into a $wpdb->query($sql); all is happy.

But replace the sprintf with a $wpdb->prepare and……

What the prepare does is it wraps the strings in single quotes as defined by the presence of the %s (string place holder) , which is good ( so you can remove them or leave them in – the wp code figures that out.) But it also wraps the table_name in single quotes. Now apparently mysql gags on this Well it chokes really badly. Instead, you can just toss in the tables variable name without any fuss as the function does all the magic ( or is it because I’m using double quotes…. I’m not sure – it’s late!)

WordPress simply smiles if you have dodgey SQL statements.

So the quotes around the table name was the first thing… Now WordPress ( by default) simply smiles at you if your SQL has errors and shows you nothing. You only know there’s a problem  on inspecting your tables only to see that things aint quite right. So I found a function that lets you “Turn on” Error Reporting for just these occassions.

Actually I had resorted to using the GUI Mysql Browser tool ( which I love ) and figured out the ‘table_name’ as being the issue.

$wpdb->print_errors() cause a slight moment of panic.

The function is $wpdb->print_errors() which you can use after a call to $wpdb->show_errors(). This will print any errors generated on the last mysql call. Basically it’s a wrapper for the standard mysql_error() function. And it worked… it was whinging ( in it’s own uninformative way ) that something was wrong. That was the apostrophes around the table_name being the culprit.

So I was happy and I ran my plugin again to update it’s database only to be greeted by a bunch ( cause I was looping ) of the following message. WordPress database error: [] in the plugin page accompanied by the SQL statement it was using. Which at about 3am has you wondering “what the….”. Anyway I dove into the file – wp-db.php in the includes folder and saw what was going on. Actually nothing…. This $wpdb->print_errors() prints a response if there is an error or not. At first the word BUG lept into my sleepy mind but on further thought it’s just showing what’s going on, Error or Not.

So it will display the “Error” with the SQL statement.
The WordPress database error: [] is a Non Error cause it’s lil square brackets are empty.

And it’s not a WordPress 3.0 thing, it’s been there a while, it’s just I’ve never used it before.

Whats the Plugin I’m writing?

Well that’s for another post -plus it’s got a bit further along than 3am so I had best be going.

On the "off chance" that you actually liked this post,
how about liking us on Facebook?...

Powered By Facebook Like Post Plugin