Escaping SQL with the wordpress API (9 posts)

OK I have a plugin where you can add simple table content and work with a few relational databases. It's an all in one CRUD system for price management.

However, with MySQL escaping is fairly simple, you escape the value and load them in. However, I am new to the wordpress API.

Am I right in thinking, you only need $wpdb->prepare(); when working with custom queries. As in if I am using the get_row api it requires raw data, but it also includes a $format parameter. So I am assuming that the $format parameter works in the same way as prepare does for custom SQL. As prepare loads in the SQL and checks if the values are strings or INTs etc.

The WordPress DBAL API does not have the ability to fetch individual rows from a query result, IMO a major shortcoming. Instead, the entire result set is fetched and stored, and you must create your own (redundant) internal loop to retrieve row-by-row results from the stored result array/object. You can do this via $wpdb->get_row() with a for() loop, or via $wpdb->get_results() with a foreach() loop. The latter option is the preferred method since it uses fewer resources, and with the ARRAY_A option, allows you to retrieve results in a much-more-familiar format.

I don't know about that. I was using the exact method you describe here... Annoying didn't describe it. However, after days or staring at my lines and lines of code I discovered this.

$current_row = $wpdb->get_row("SELECT * FROM {$table} WHERE id = {$id}");
echo $current_row->$field_name; //This will out put the value from only one field

Due to how many times I needed this in my plugin, I managed to shave about 100 lines out of my code. I then put that inside a function and fed in the parameters. Maybe you know this but I found it useful.

I would strongly recommend using prepare whether or not you cast. I know this seems kind of ridiculous, but for maintenance sake, just accept parameters into your function and then prepare them into the statements.

Also, sorry for the weird formatting on that second exactly about. I tried to use backticks inside the prepared statement around the field name and apparently it failed :(

For inserts and updates, don't forget that WPDB has utility functions for those as well! It might be worth reading through wp-db.php in its entirety and familiarizing yourself with all that stuff.

The problem is that I like to reuse my code a lot. So I have 1 function that works with so many forms. Prepare forces a very strict knowledge of what is going to be going into the box, this is a problem since you can only put raw data into the $data section, then you have to state %S %d %f one form will be a string another is a float, another is an integer. It seems so counter productive. I would much prefer to be able to escape as I build the array.

To clarify though I only have to escape human input yes? so for example the user never gets to say which table they are working with that is a hard coded string. I wouldn't need to escape that no?

Code reuse is good, but I don't know if there is a need for a general query function when one already exists. I'd recommend separating your functionality into dedicated pieces and letting $wpdb be the abstraction layer.

Yes, you only need to escape human input or things that can be filtered. If the table is a hardcoded string, it doesn't need to be escaped.

I know I am revisiting this, but I have one more question. If I escape the CREATE part of the CRUD do I need to escape the READ parts. For example if when adding a new value that value is entered as an escaped value. Would I have to escape it when it is read back and displayed on screen?