Tag Archives: database

Dominik Schilling 12:23 pm on July 13, 2016
Tags: , database,   

dbDelta() updates in 4.6

dbDelta() is a function which helps to modify the database based on specified SQL statements. In WordPress 4.6 this function has been updated to resolve some long standing bugs.

Normalized index definitions

Previously, dbDelta() compared the raw SQL data for index definitions with the output of SHOW INDEX FROM. This required a specific format so indices are not unnecessarily re-created. But this format wasn’t always ensured, until now. See [37583]:

  • dbDelta() now parses the raw index definition to extract the type, name and columns. With this data a normalized definition is build (#20263, #34873).
  • Core standardize now on uppercase types (#34871) and on ‘KEY’. ‘INDEX’ is only a synonym for ‘KEY’.
  • ASC and DESC definitions are currently unused by MySQL, dbDelta() ignores them now too (#34959).
  • Spaces in index definitions don’t create duplicate indices anymore (#34869).

Backtick all the things

Index names (#20263), index column names (#20263), names in index change queries (#20263) and other queries (#31679) are now escaped with backticks to improve compatibility with names which are reserved keywords.

Support for SPATIAL keys

dbDelta() already supported spatial fields (by virtue of not checking field types), so it’s nice to round that out with spatial key support, too. See #36948.

 

If you’re using dbDelta() in your plugin please test it with WordPress 4.6 and report any issues in the comments or on Trac.

There are still a few issues with dbDelta(), can you help us to fix them? 🔨

#4-6, #database, #dev-notes

Scott Taylor 5:49 pm on June 20, 2014
Tags: , database, , ,   

like_escape() is Deprecated in WordPress 4.0

@miqrogroove has written a blog post on his personal blog explaining why like_escape() has been deprecated in WordPress 4.0. It has been reposted below.

Plugin authors and website developers who work with WordPress database queries should notice an important change coming in WordPress 4.0.

The function like_escape() is no longer used in WordPress core code. It is still available as a deprecated function, so it still works in any existing plugins that rely on it. However, a new and different function is available that should be used in all new code.

Deprecated means that anyone using code that calls like_escape() with WP_DEBUG enabled will see an error message. If WP_DEBUG_LOG is also enabled, the error message will appear in the /wp-content/debug.log file.

Let’s look at an example of core code where I removed like_escape() and implemented the new function $wpdb->esc_like().

3.9 Old Style

$search_orderby_s = like_escape( esc_sql( $q['s'] ) );
$search_orderby .= "WHEN $wpdb->posts.post_title LIKE '%{$search_orderby_s}%' THEN 1 ";

What did this do? It was an old snippet from /wp-includes/query.php that set up a search for post titles. The input $q['s'] was escaped using two functions before it was added to the post_title LIKE expression. Now let’s see how I replaced that snippet in the next version.

4.0 New Style

$like = '%' . $wpdb->esc_like( $q['s'] ) . '%';
$search_orderby .= $wpdb->prepare( "WHEN $wpdb->posts.post_title LIKE %s THEN 1 ", $like );

There are two important differences to notice.

  • I changed the like_escape() call to $wpdb->esc_like().
  • I changed the esc_sql() call to $wpdb->prepare().

The second change is important because esc_sql() is not secure if it is called before, or inside, the call to the new function $wpdb->esc_like(). By relying on the preferred style of the function prepare(), I can easily see that each instance of $wpdb->esc_like() will run first instead of last.

4.0 Alternative Style

Here is something that still works, but I avoided using it. Notice the old query is unchanged. It is critically important to call the two escaping functions in the correct order when using $wpdb->esc_like().

$search_orderby_s = esc_sql( $wpdb->esc_like( $q['s'] ) ); // This is the correct order.
$search_orderby .= "WHEN $wpdb->posts.post_title LIKE '%{$search_orderby_s}%' THEN 1 ";

How Should I Get My Code Ready for 4.0?

The nice thing about deprecated functions is that you can still use them and they don’t change. Your existing code should work fine.

When you write new code, remember that using $wpdb->esc_like() is not compatible with WordPress 3.9. This should be avoided if you need compatibility with old versions. When you are ready to adopt 4.0 as your minimum version, consider using the new function.

If you have a specific need for the new function in old versions of WordPress, I suggest copying the new function into your plugin under a different name. This would be the simplest solution, but rarely necessary.

Why Did like_escape() Change to $wpdb->esc_like()?

There were several problems with the old function that could not be fixed.

  • Documentation said the function’s output was safe to use in SQL queries. That was not correct.
  • The function’s output was not fully compatible with LIKE expressions in MySQL.
  • The function had been used many different ways in core code, some of which were incompatible with the desired output.
  • Changing the old function instead of creating a new one would have caused many security problems in plugins.
  • The function was related to $wpdb because of its MySQL syntax, which does not work on other databases.

Is There a Security Problem with like_escape()?

The old function like_escape() was not intended to be used in any security sensitive context. There are no security problems when it is used correctly.

With that said, I am concerned that plugin authors frequently confused the old function like_escape() with esc_sql(), which was used for security. The documentation for like_escape() was misleading and very confusing about this point.

Just remember, like_escape() does not provide any security for your database!

So What Does $wpdb->esc_like() Do Anyway?

Whenever user input or other raw data are copied into a WordPress query, the data must be escaped using $wpdb->prepare() or esc_sql(). This prevents certain characters, such as quotes, from getting confused with SQL commands.

In a LIKE expression, there are additional special characters that are used as wildcards to search for partial matches in the database. Those wildcard characters have to be escaped from the user input so that they are not confused with the wildcards added by the programmer.

Before adding user input to this type of search query, $wpdb->esc_like() should be called for compatibility, and then $wpdb->prepare() must be called for security, in that order.

How to Use $wpdb in Functions

It is very rare to use $wpdb->esc_like() without also running a query. But just in case you want to …

function my_search( $input ) {
    global $wpdb;
    $escaped = $wpdb->esc_like( $input );
    ...
}

… remember to reference $wpdb as a global variable.

#4-0, #database, #dev-notes, #query, #security

Simon Wheatley 2:04 pm on June 12, 2014
Tags: , database   

In yesterday’s Weekly Developer Chat various minor schema change…

In yesterday’s Weekly Developer Chat, various minor schema change tickets were discussed. We would want to address any changes in as efficient a fashion as possible, and have discussed using the pre_schema_upgrade() function rather than dbDelta(), so we can control the queries more precisely.

Below is a list of the tickets discussed yesterday, along with which tables they affect. Please add any questions, concerns, additional tickets, or +1’s for this work in the comments.

wp_comments

  • comment_author_email – #21435 – “wp-includes/comment.php line85 causes slow query due to the non-indexed column” raised by @matsubobo (proposes adding an index to comment_author_email)
  • multiple-column indexes – #15499 – “Add an index for get_lastpostmodified query” raised by @simonwheatley (proposes adding an index on post_type, post_status, post_date_gmt and another on post_type, post_status, post_modified_gmt)

wp_options

  • option_name – #13310 – “Extend option_name to varchar(255)” raised by @scribu

wp_posts

  • post_name – #10483 – Increase field length from 200 to 400. #21212 – Reduce index length to 191 for InnoDB.
  • guid – #18315 – “Add an index to the GUID column in the posts table” raised by @alexkingorg
  • post_password – #881 – “Lengthen password field for protected posts” raised by @ScytheBlade1

wp_terms

  • slug – #22023 – “Remove UNIQUE for slug in wp_terms” raised by @nacin (related). #16230 – Increase field length from 200 to 400. #21212 – Reduce index length to 191 for InnoDB

wp_term_taxonomy

  • modify existing index – #5034 – “Impossible to have duplicate category slugs with different parents” raised by @snakefoot (proposes adding an index on term_id, taxonomy, parent)

#4-0, #database

Gary Pendergast 6:20 am on April 7, 2014
Tags: , database, , ,   

MySQL in WordPress 3.9

In WordPress 3.9, we added an extra layer to WPDB, causing it to switch to using the mysqli PHP library, when using PHP 5.5 or higher.

For plugin developers, this means that you absolutely shouldn’t be using PHP’s mysql_*() functions any more – you can use the equivalent WPDB functions instead.

mysql_query()

There are a few different options for replacing the query functions, depending on what you want to do:

As a drop in replacement to run a query that you don’t expect a return value from (i.e., an INSERT, UPDATE or DELETE query), use $wpdb->query(). This will always return the number of rows effected by the query.

Alternatively, $wpdb->insert(), $wpdb->update(), $wpdb->delete() and $wpdb->replace() are all helper functions that will automatically escape your data, then generate and run the queries for you. Ideally, you should never need to write an SQL statement!

mysql_fetch_*()

If you have a SELECT query, for which you’d normally do a mysql_query() followed by a mysql_fetch_*(), WPDB lets you combine this into one function call.

To get all of the results from a query that returns more than one row, use $wpdb->get_results() to return an array of objects containing your data.

There are also some shortcut functions for common usage:

If you only need a single row from your query, $wpdb->get_row() will return just the data object from that row.

If you only need a single column from a single row, $wpdb->get_var() will return only that field.

And if you need a single column, $wpdb->get_col() will return an array of all the data from that column.

mysql_real_escape_string()

For a drop in replacement, you can use esc_sql(). That said, we strongly recommend switching to $wpdb->prepare(), instead. We have a pretty thorough tutorial available for $wpdb->prepare().

mysql_insert_id()

If you need to get the Insert ID from the last query, $wpdb->insert_id is where you need to look.

Updating your plugin to use WPDB will also future proof it for if we make changes to how WordPress connects to the database – we’ll always maintain backwards compatibility with the current WPDB interface.

For more reading, check the WPDB Codex page, and #21663.

If you’re using MySQL in a way that I haven’t covered here, please post it in the comments, we’d be happy to help you out!

#3-9, #database, #dev-notes, #mysql, #wpdb