Title: Escaping SQL with the wordpress API
Last modified: August 21, 2016

---

# Escaping SQL with the wordpress API

 *  [gcoulby](https://wordpress.org/support/users/gcoulby/)
 * (@gcoulby)
 * [12 years, 3 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/)
 * 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.
 * Or. do I need to prepare every $wpdb statement?

Viewing 8 replies - 1 through 8 (of 8 total)

 *  [Dion](https://wordpress.org/support/users/diondesigns/)
 * (@diondesigns)
 * [12 years, 3 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695161)
 * [http://codex.wordpress.org/Function_Reference/esc_sql](http://codex.wordpress.org/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.
 *  Thread Starter [gcoulby](https://wordpress.org/support/users/gcoulby/)
 * (@gcoulby)
 * [12 years, 3 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695195)
 * 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.
 *  [nickohrn](https://wordpress.org/support/users/nickohrn/)
 * (@nickohrn)
 * [12 years, 3 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695198)
 * 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`](https://github.com/WordPress/WordPress/blob/master/wp-includes/wp-db.php#L1782)
   instead of `get_row`:
 * `echo $wpdb->get_var($wpdb->prepare("SELECT`{$field_name}`FROM {$table} WHERE
   id = %d", $id));`
 *  Thread Starter [gcoulby](https://wordpress.org/support/users/gcoulby/)
 * (@gcoulby)
 * [12 years, 3 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695205)
 * 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
 *  [nickohrn](https://wordpress.org/support/users/nickohrn/)
 * (@nickohrn)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695219)
 * 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.
 *  Thread Starter [gcoulby](https://wordpress.org/support/users/gcoulby/)
 * (@gcoulby)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695221)
 * 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?
 *  [nickohrn](https://wordpress.org/support/users/nickohrn/)
 * (@nickohrn)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695223)
 * 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.
 *  Thread Starter [gcoulby](https://wordpress.org/support/users/gcoulby/)
 * (@gcoulby)
 * [12 years, 2 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695320)
 * 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?

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘Escaping SQL with the wordpress API’ is closed to new replies.

## Tags

 * [escaping](https://wordpress.org/support/topic-tag/escaping/)
 * [MySQL](https://wordpress.org/support/topic-tag/mysql/)
 * [prepare](https://wordpress.org/support/topic-tag/prepare/)
 * [sql](https://wordpress.org/support/topic-tag/sql/)
 * [wpdb](https://wordpress.org/support/topic-tag/wpdb/)

 * In: [Hacks](https://wordpress.org/support/forum/plugins-and-hacks/hacks/)
 * 8 replies
 * 3 participants
 * Last reply from: [gcoulby](https://wordpress.org/support/users/gcoulby/)
 * Last activity: [12 years, 2 months ago](https://wordpress.org/support/topic/escaping-sql-with-the-wordpress-api/#post-4695320)
 * Status: not resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics