Escaping SQL with the wordpress API

https://codex.www.ads-software.com/Function_Reference/esc_sql

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.

Just to be safe, use the prepare method. No need to be reckless ??

$current_row = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table} WHERE id = %d", $id));

If you really only need the one field, though, maybe try get_var instead of get_row:

echo $wpdb->get_var($wpdb->prepare("SELECT{$field_name}FROM {$table} WHERE id = %d", $id));

OK nickohrn, OK I was having problems with this all because of miss-placed comma as I see now, However, do you see an issue with using casting?

$id = (double) $_POST["id"];

or would I still need to prepare that?

I’m not too worried about the select statements so much as they just require either my input from the code or a numeric. However, its the edit rows and deletes that worry me

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?