Proposal: Clarifying Core’s Database Support Policy

TL;DR Over 37% of WordPress sites are running a version of database software that has reached end of life and is no longer receiving maintenance or security updates. To encourage broader adoption of secure and supported database versions, WordPress should clarify that only LTS releases of MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. and MariaDB are officially supported by excluding those with short lifespans or no guarantee of backward compatibility.

As a community, we frequently champion efforts to keep software and tools updated to their latest versions. This typically surfaces for users in the form of PHPPHP The web scripting language in which WordPress is primarily architected. WordPress requires PHP 7.4 or higher upgrades, and for contributors with tools like Node.js and npm. Database software is equally important to site health but has historically received far less attention.

The release models followed by supported database types have changed quite a bit since the early days of WordPress. With that comes a need to update the project’s official policy for clarity. A clearer policy lends to a wider understanding of the expectations and requirements for maintaining WordPress sites.

But before getting into the changes being proposed, let’s define the various types of releases and examine current usage data before outlining a few things for discussion.

Note: If you’re already familiar with the release models of MariaDB and MySQL, you can skip the context directly to the proposal part of the post.

Supported Database Software

There are currently two types of database software officially supported for running WordPress: MySQL and MariaDB. While it’s true that SQLite powers Playground, standard installs of WordPress do not currently support SQLite without installing the feature plugin, which is being actively developed and tested.

Release Types

Both software projects have made several changes to the terminology used to reference releases, making it confusing and inconsistent. For the sake of clarity, here are the different terms associated with releases of MySQL and MariaDB.

Note: All asterisks (*) below indicate versions that have reached end of life (EOL) and are no longer supported upstream. Also, EOL does not always equal “unsupported by WordPress” (more on that later).

Long Term Support (LTS)

LTS versions are published by both projects. These are the most common versions found in the wild. They are actively supported for 5 years from the initial release. After initial release, data formats are not changed, no new functionality is introduced, and no features are removed.

While MariaDB aims to release a new LTS version annually, MySQL publishes a new LTS version every 2 years (starting with 8.4). The list of releases labelled as LTS to date is as follows:

VersionProjectInitial ReleaseEOL Date
8.4MySQLApril 30, 2024April 2032
8.0MySQLApril 19, 2018April 2026
11.8MariaDBJune 5, 2025June 2030
11.4MariaDBNovember 22, 2024May 29, 2029
10.11MariaDBFebruary 16, 2023February 16, 2028
10.6MariaDBJuly 6, 2021July 6, 2026
10.5*MariaDBJune 24, 2020June 24, 2025
10.4*MariaDBJune 18, 2019June 18, 2024

The following releases were treated as LTS versions, but were not explicitly labelled as such:

VersionProjectInitial ReleaseEOL Date
5.7*MySQLOctober 21, 2015October 21, 2023
5.6*MySQLFebruary 5, 2013February 5, 2021
5.5*MySQLDecember 3, 2010December 3, 2018
5.1*MySQLNovember 14, 2008December 31, 2013

Innovation Releases

Though both projects use this classification, only the MySQL project actively uses it as described in this section (see the next section for how MariaDB currently uses the designation). Innovation releases aim to provide early access to new features and improvements without having to wait the full 2 years for the next MySQL LTS version to be published.

The following types of changes are included in new innovation releases:

  • New features planned for the next LTS
  • Security fixes
  • Bug fixes
  • New deprecations
  • Removal of features
  • Behavior changes.

These releases are Generally Available and are acceptable to use in production environments. They are published once per quarter (every 3-4 months). When the next innovation release is published, all support for the previous one is dropped, including security fixes. Because the lifespan of each innovation release is extremely short, consistent regular attention is required to remain supported and secure.

The following is a list of MySQL innovation releases.

VersionInitial ReleaseEOL Date
9.3April 15, 2024July 2025
9.2*January 21, 2025April 15, 2025
9.1*October 14, 2024January 21, 2025
9.0*July 1, 2024October 15, 2024
8.3*January 16, 2024July 1, 2024
8.2*October 25, 2023January 16, 2024
8.1*July 18, 2023October 25, 2023

MariaDB used this label for about 2 years. During that time, they also published a new innovation release every quarter. However, their innovation releases were supported for 1 year after initial GA release. These releases also differed from MySQL in that caution was advised when using these. As of the publish date of this post, all MariaDB innovation releases have reached EOL.

VersionInitial ReleaseEOL Date
11.2*November 21, 2023November 21, 2024
11.1*August 21, 2023August 21, 2024
11.0*June 7, 2023June 6, 2024
10.10*November 17, 2022November 14, 2023
10.9*August 22, 2022August 22, 2023
10.8*May 21, 2022May 20, 2023
10.7*February 14, 2022February 9, 2023

Rolling GA (MariaDB only)

Though they’re similar in some ways, MariaDB now has replaced innovation releases with rolling GA releases. Rolling GA releases are published every quarter (3-4 months) and receive no patchpatch A special text file that describes changes to code, by identifying the files and lines which are added, removed, and altered. It may also be referred to as a diff. A patch can be applied to a codebase for testing. versions (the GA release is final). All support for the previous rolling release is dropped once a new one is published.

The term innovation release is still used but now refers to the preview (X.Y.0) and RCrelease candidate One of the final stages in the version release cycle, this version signals the potential to be a final release to the public. Also see alpha (beta). (X.Y.1) pre-release versions for each rolling release. Rolling GA releases are intended for production use, but the innovation releases are not.

Rolling Release VersionsInitial ReleaseEOL Date
12.1 (currently in preview innovation release)TBD
12.0 (currently RC innovation release)TBD
11.7*February 13, 2025May 12, 2025
11.6*November 22, 2024February 13, 2025
11.5*August 15, 2024November 21, 2024
11.3*February 19, 2024May 16, 2024

Major Releasemajor release A release, identified by the first two numbers (3.6), which is the focus of a full release cycle and feature development. WordPress uses decimaling count for major release versions, so 2.8, 2.9, 3.0, and 3.1 are sequential and comparable in scope.

MySQL refers to major versions in the same way as WordPress (X.(Y+1)).

For MariaDB, this term has meant different things during different timeframes.

10.3 and earlierX.(Y+1)Every new release
10.4-11.2X.(Y+1)Each version was considered major because of binary incompatibility and the addition of new features. LTS or short-term become more common descriptors as they indicate length of support (similar to WordPress).
Current day *(>= 11.3)(X+1).0Only when the first number changes (similar to PHP).

While the LTS designation did not exist yet, versions 10.3 and earlier were treated in a similar way.

  • Supported for 3-5 years (sometimes longer)
  • Bug fixes and security updates were provided
  • New features were sometimes included in minor (X.Y.(Z+1)) releases, but compatibility was always maintained.

All MariaDB major releases <= 10.3 have reached EOL. They’re listed below for completeness:

MariaDB Major Versions <= 10.3Initial ReleaseEOL Date
10.3*May 25, 2018May 25, 2023
10.2*May 23, 2017May 23, 2022
10.1*October 17, 2015October 17, 2020
10.0*March 31, 2014March 31, 2019
5.5*April 11, 2012April 11, 2020
5.3*February 29, 2012March 1, 2017
5.2*November 10, 2010November 10, 2015
5.1*February 1, 2010February 1, 2015

While some online resources show the LTS label next to these versions, they were never officially labelled as such.

Short-term (MariaDB Only)

This term was eventually replaced by innovation release. This applied to MariaDB 10.4 in addition to the MariaDB versions listed above under Innovation Releases. As of the publish date for this post, all short-term releases have reached EOL.

Current Database Usage Data

Here is the usage data according to the WordPress.org Stats page as of June 18, 2025 summarized in a few different ways:

By Software

VersionUsage
MariaDB – all versions52.8%
MySQL – all versions47.2%

By Version Type

Release TypeUsage
MariaDB LTS (includes old major releases without LTS official designation)51.17%
MySQL LTS (includes old major releases without LTS designation)46.74%
MariaDB Innovation/Short-term/Rolling Releases1.52%
MySQL Innovation Releases0.07%
Other0.5%

By Support Status

Support StatusUsage
EOL versions (MariaDB)11.43%
EOL versions (MySQL)26.01%
Supported (MariaDB)41.56%
Supported (MySQL)21.05%

Observations

There are a few interesting things that stand out from the usage data.

  • As of March 2025, MariaDB now powers more WordPress sites than MySQL. This is particularly interesting because as of January 2024, the split was ~63%/37% in favor of MySQL. Increases in licensing costs and changes to widely used distributions during 2023 and 2024 are likely the considerable drivers of this surge.
  • Over 37% of WordPress sites are running a version of database software that has reached end of life and is no longer receiving maintenance or security updates.
  • The highest adoption of any innovation/short-term/rolling release was MariaDB 11.3, which reached 0.15%.
  • No other innovation/short-term/rolling releases have surpassed more than 0.10% adoption at any given point in time.

Current State of Database Support in WordPress

Despite MySQL and MariaDB having different release models that drop support in different ways in non-sequential order, WordPress has a very binary support policy. As of the publish date of this post, the support policy is as follows:

  • Recommended: MySQL >= 8.0 OR MariaDB >= 10.6
  • Required: MySQL OR MariaDB >= 5.5.5

A few notes:

  • MariaDB 10.5 reached EOL on June 24th. The recommended version was updated to >= 10.6 on June 18, 2025 (see #60319 and #meta-8013).
  • The minimum required version was last bumped in late 2023 (see [57173]).

The policy as currently stated is lacking any guidance around which types of releases are supported. As demonstrated above, this is complicated and convoluted. The policy should be expanded a bit to cover release types and not just versions.

Proposal: Clarify Supported Release Types

While described as production-grade, innovation releases have several shortcomings when considering the majority of WordPress sites.

  • Features and changes included in innovation/rolling releases are considered stable, but they can be modified or even removed entirely in future releases. No functionality can be considered supported until included in an LTS release.
  • Their ephemeral nature makes it very difficult to ensure compatibility with WordPress when released. Depending on how release timelines overlap, there could be 2 (or more) innovation/rolling versions published in between major WordPress releases.
  • Their extremely short lifespans could potentially leave large numbers of sites running insecure versions.
  • Updating database software is often tied to factors out of the site owner’s control (waiting for server distributions to be updated and made available through your hosting, platform compatibility limitations, compliance/licensing requirements, etc.).
  • For both projects, updating these release types is complicated. Updates must be applied sequentially. For example, upgrading from 9.1 to 9.3 is not supported (9.1 must first be updated to 9.2 before updating to 9.3). This is not required when upgrading to the next LTS.
  • Over the last two years, these release types have not shown significant usage, even when combining the numbers across database types.
  • Because the changes in these releases are not guaranteed to be backwards compatible, committing to full compatibility with these releases is not a good use of contributor time and could result in making code harder to maintain.

Changes to the Database Recommendations

This proposal is suggesting that the recommendations for running WordPress be updated as follows:

  • No change: The oldest LTS versions actively supported upstream should continue being used as the recommended minimums.
  • Add to the Requirements page, CoreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress./Hosting/Support/Other? Handbooks: Innovation and rolling GA releases are explicitly not recommended for production sites. 
  • Add to the Hosting Handbook: Hosting companies are explicitly advised not to use Innovation and Rolling GA releases as the default for new sites.

The WordPress PHPUnit testing workflow currently runs the test suite using the most recent MySQL innovation and MariaDB rolling GA releases. This practice helps to make contributors aware of potential incompatibilities and breaking changes so they can be addressed prior to the next LTS version. This practice will continue.

Because of this, it’s reasonable to expect WordPress to have very few (if any) issues when running these releases. However, this is not guaranteed as contributors must weigh the benefits of making changes for the sake of compatibility with the non-zero chance that the feature is changed or removed before the next LTS release.

Because innovation and rolling GA releases are never supported, only LTS versions are eligible as new minimums when considering changes to the minimum required versions of MariaDB/MySQL to run WordPress in the future. 

In summary, someone is free to run innovation and rolling GA releases, but they do so at their own risk.

Other Considerations

As a part of this, ways to further educate and empower the user should also be explored.

  • Should servehappy be expanded to display a dashboard notice to the user when their site is running an outdated or unsupported version of MariaDB/MySQL (see #63634)?
  • Can there be additional context for the database checks in Site Health to detect innovation/rolling GA releases explaining why they should consider using an LTS version instead?
  • Are there ways to enhance WP-CLIWP-CLI WP-CLI is the Command Line Interface for WordPress, used to do administrative and development tasks in a programmatic way. The project page is http://wp-cli.org/ https://make.wordpress.org/cli/ to properly advise about these risky release types?
  • Are any metaMeta Meta is a term that refers to the inside workings of a group. For us, this is the team that works on internal WordPress sites like WordCamp Central and Make WordPress. changes required to better inform the site that these release types are not explicitly supported?
  • Do the auto-update related classes require any changes?

Working Together

Reliable and supported database software is foundational to the performance, security, and longevity of WordPress a site. Historically, efforts to promote upgrades among hosts and site owners have been limited and inconsistently prioritized. Clarifying the types of database releases that WordPress supports and is compatible with marks an important first step. It enables contributors, hosts, and site owners to move toward more secure and sustainable versions of MariaDB and MySQL.

Props @johnbillion, @flixos90, @l1nuxjedi, and @annezazu for peer review.

#database, #mariadb, #mysql, #site-health

Let’s make WordPress officially support SQLite

WordPress can be (and is) used for any kind of site, regardless of size or complexity. Some common use-cases include:

  • Single-page landing sites
  • Simple company sites with just a few pages. These sites are usually rarely updated and are essentially static sites with an administration aspect.
  • Simple, single-user blogs
  • Complex news sites
  • e-Commerce sites
  • Full-fledged CMS solutions

Part of the success of WordPress is because it is extendable, hookable, and can be used and tweaked to accomplish almost any task on the web.

However, one aspect of WordPress has never changed, regardless of the increase in WP’s use cases and popularity: The database. WordPress requires MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/./MariaDB to be installed on a site. MySQL is arguably only optimal for some of the scenarios: The “mid-tier” range of site types.

Large sites usually implement custom database stacks depending on their specific needs, so are beyond the scope of this proposal.

On the lower end of the spectrum, there are small and simple sites. These are numerous and consist of all the blogs, company pages, and sites that don’t have thousands of users or thousands of posts, etc. These websites don’t always need the complexities of a MySQL/MariaDB database. The requirement of a dedicated MySQL server increases their hosting cost and the complexity of installation. On lower-end servers, it also decreases performance since the same “box” needs to cater to both a PHPPHP The web scripting language in which WordPress is primarily architected. WordPress requires PHP 7.4 or higher and a MySQL/MariaDB server.

The ideal scenario

Ideally, WordPress would allow us to choose the type of database during installation. That could be done using an installation guide, or a simple constant in wp-config.php. To accomplish that, WordPress would need to have a database-abstraction layer. This is not an innovative or radical idea in the CMS space; Drupal has had a solid database-abstraction layer for more than a decade. Laravel, Symfony, and others also include ORMs that allow using multiple database types.

Building a database abstraction layer for WordPress would be a colossal task – though it might be one that, at some point in the future, we may have to undertake to ensure the project’s continued evolution and longevity.

A middle ground

As a middle ground, we could implement a solution for the bottom tier: small to medium sites and blogs. These sites don’t necessarily need a full-fledged MySQL database.

SQLite seems to be the perfect fit:

  • It is the most widely used database worldwide
  • It is cross-platform and can run on any device
  • It is included by default on all PHP installations (unless explicitly disabled)
  • WordPress’s minimum requirements would be a simple PHP server, without the need for a separate database server. 
  • SQLite support enables lower hosting costs, decreases energy consumption, and lowers performance costs on lower-end servers.

Implementing SQLite in WordPress CoreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress.

Using SQLite in WordPress is, at this point, simple; There are implementations out there that have been around and evolving for more than 8 years. They have been thoroughly tested and proved to work seamlessly. These implementations are drop-in wp-content/db.php files that users can add to their installation; they are not hard to use. 

However, most people are not aware of them. They are not aware that they have the option to buy cheaper hosting sans-mysql and then install WordPress using an SQLite database. Nor should they have to know about it… After all, they just want a simple company site or a blogblog (versus network, site)

WordPress could officially support SQLite by including one of the existing SQLite implementations in Core. We would need to ensure that it is properly tested and supported, and additionally, raise awareness and expose the option to users.

Why should this be in Core and not a pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party

Picking a database type is something that should happen when a site is first installed. It is not something that should be done after the fact because that would require migrating data from one database to another, which can often be complex. 

WordPress includes the MySQL implementation in Core, so if we support SQLite then that implementation should live alongside it. 

Data migrationMigration Moving the code, database and media files for a website site from one server to another. Most typically done when changing hosting companies. can (and should) be in a plugin to facilitate migrations for existing sites should they wish to do that, but the database engine itself belongs in Core. 

That will ensure that the implementation is properly supported, properly tested, and WordPress will be able to benefit, as detailed in the following section.

What would the benefits of SQLite be?

Officially supporting SQLite in WordPress could have many benefits. Some notable ones would include:

  • Increased performance on lower-end servers and environments.
  • Potential for WordPress growth in markets where we did not have access due to the system’s requirements.
  • Potential for growth in the hosting market using installation “scenarios”.
  • Reduced energy consumption – increased sustainability for the WordPress project.
  • Further WordPress’s mission to “democratize publishing” for everyone.
  • Easier to contribute to WordPress – download the files and run the built-in PHP server without any other setup required.
  • Easier to use automated tests suite.
  • Sites can be “portable” and self-contained.

Next steps

The next steps would need to be discussed in the comments section of this proposal. If there is consensus to implement SQLite in WordPress Core, an outline of the next steps would look something like this:

  • Create the necessary TracTrac An open source project by Edgewall Software that serves as a bug tracker and project management tool for WordPress. tickets
  • Decide how the database type would be defined. The most simple scenario would be a DATABASE_TYPE constant in wp-config.php, allowing users to choose if their new site would use a MySQL or SQLite database, but there can be other solutions that come up during a later discussion.
  • Port an SQLite implementation to WordPress Core, applying the necessary changes like coding standards, in-code documentation, migrate tests, etc.
  • Test WordPress Core functionality with SQLite
  • Outreach to plugin developers for testing.

The subject of database abstractions and using SQLite was discussed at length during WCEU 2022, in an unofficial capacity, in the hallways. This post is a distilled culmination of these conversations to bring the discussion to the broader community for serious consideration.

Props @zieladam, @mamaduka, @jonoaldersonwp, @tweetythierry, @SergeyBiryukov, @joostdevalk, @dingo_d, @desmith, @crixu, @williampatton, @costdev, @adamsilverstein, @JavierCasares, @robinwpdeveloper, @aaemnnosttv, @jessibelle for reviewing and contributing to this proposal.

#database, #install, #performance, #sustainability

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).
  • CoreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress. standardize now on uppercase types (#34871) and on ‘KEY’. ‘INDEX’ is only a synonym for ‘KEY’.
  • ASC and DESC definitions are currently unused by MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/., 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 pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party 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

like_escape() is Deprecated in WordPress 4.0

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

PluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party 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 coreCore Core is the set of software required to run WordPress. The Core Development Team builds WordPress. 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 MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/..
  • 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

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 categoryCategory The 'category' taxonomy lets you group posts / content together that share a common bond. Categories are pre-defined and broad ranging. slugs with different parents” raised by @snakefoot (proposes adding an index on term_id, taxonomy, parent)

#4-0, #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 PHPPHP The web scripting language in which WordPress is primarily architected. WordPress requires PHP 7.4 or higher library, when using PHP 5.5 or higher.

For pluginPlugin A plugin is a piece of software containing a group of functions that can be added to a WordPress website. They can extend functionality or add new features to your WordPress websites. WordPress plugins are written in the PHP programming language and integrate seamlessly with WordPress. These can be free in the WordPress.org Plugin Directory https://wordpress.org/plugins/ or can be cost-based plugin from a third-party 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 MySQLMySQL MySQL is a relational database management system. A database is a structured collection of data where content, configuration and other options are stored. https://www.mysql.com/. 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