Filter Rails JSON input with route constraints

Following the recent YAML parsing vulnerabilities in Rails, I decided to act on an idea I had a few months ago: using route constraints to define strict API contracts in Rails.

Sadly, it does not protect against the YAML parsing problem (and the probable similar vulnerabilities we will see in the following months), because the request is interpreted before going through the route constraints. But it can protect from the mass assignment vulnerability., and probably some SQL injections.

Here is the idea: Rails 3 introduced the route constraints, a way to execute fonctions on the request before it is passed to the controllers. By combining it with the json-schema gem, we can filter the JSON input quite easily.

For the following example data:

{"echo" : "blah", "nb" : 1, "data": [1, 2, 3]}

We can define the following schema:

    "type": "object",
    "$schema": "",
    "id": "#",
    "required": false,
    "additionalProperties": false,
    "properties": {
        "data": {
            "type": "array",
            "id": "data",
            "required": false,
            "items": {
                "type": "number",
                "id": "0",
                "required": false
        "echo": {
            "type": "string",
            "id": "echo",
            "required": false
        "nb": {
            "type": "number",
            "id": "nb",
            "required": false

(Use the JSON schema generator to create your own)

Save this schema to “data.schema” and add “json-schema” to your Gemfile. You will then be able to filter inputs with code like the following “config/routes.rb”:

require "json-schema"
class LolJSONConstraint
  def matches?(request)
    if(request.headers["CONTENT_TYPE"] == "application/json")
      JSON::Validator.validate("data.schema", request.headers["action_dispatch.request.request_parameters"])

Yamlvuln::Application.routes.draw do
  resources :posts, :constraints =>

The constraint will load the schema, and apply it to the incoming data, and return a 404 error if the JSON is invalid. The “additionalProperties” set to false in the schema is required to refuse the properties you didn’t define and protect the application from mass assignment.

If I tried, for example, to send the following JSON to the application, there would be an error:

{"echo" : "blah", "nb" : "UNION ALL SELECT LOAD_FILE(CHAR(34,47,101,116,99,47,112,97,115,115,119,100,34))", "data": [1, 2, 3]}

As I said before, it is not safe against the YAML parsing vulnerability. Also, I did not really test the performance of this. But it is still a nice and easy solution for API filtering.


Harden WordPress using database permissions

Here is a small idea that I would like to throw into the world: most web applications use only one database user for most operations (installation, administration, common usage). Couldn’t we harness the database to protect a bit your data?

How to

This is how you could do it:

  • Create one user (called ‘user’) with full privileges on the database
  • Create another user with no privileges (let’s call him ‘read’)
  • Create a copy of wp-config.php that you will name wp-config-admin.php
  • Write the ‘read’ credentials in the wp-config.php and the normal credentials in wp-config-admin.php (don’t forget to use different auth, secure auth, logged in and nonce keys)
  • Create a copy of wp-load.php that you will name wp-load-admin.php
  • Replace in wp-load-admin.php the reference to wp-config.php by wp-config-admin.php
  • Replace in wp-login.php and wp-admin/* the references to wp-load.php by wp-load-admin.php
  • Now, you can use the admin interface, create posts, etc.
  • Grant some permissions to the ‘read’ database user: GRANT SELECT ON `db`.* TO ‘read’; GRANT INSERT, UPDATE ON `db`.`wp_comments` TO ‘read’;

That was a bit of work, but not that hard! So, what did we do here? We created a user for the admin interface with full privileges on the database (create/update posts, change the taxonomy, approve the comments, etc) and another one for the front end interface, with only read privileges on all tables (that bothers me too, but read on).

This means that SQL injections, either in plugins or in WordPress code (out of the admin panel) will be much harder to implement with this setup. Beware of the custom tables for some plugins. Those will require specific permissions. Depending on the plugin, some could be read only for common usage.

Going further

That’s nice, but not enough in my opinion. As I said, the full select permission for the ‘read’ user bothers me. Couldn’t we restrict a bit the permissions on wp_users? Some of the columns are needed, but do we need to access the user_pass column? Also, the “ALL PRIVILEGES” for ‘user’ is a bit too much. Do we really use the “FILE” privilege (out of SQL injections :D)?

Without further ado, here are the SQL commands you should use:

GRANT SELECT, INSERT, UPDATE ON `db`.`wp_comments` TO ‘read’;

GRANT SELECT ON `db`.`wp_commentmeta` TO ‘read’;

GRANT SELECT ON `db`.`wp_links` TO ‘read’;

GRANT SELECT ON `db`.`wp_options` TO ‘read’;

GRANT SELECT ON `db`.`wp_term_taxonomy` TO ‘read’;

GRANT SELECT ON `db`.`wp_usermeta` TO ‘read’;

GRANT SELECT ON `db`.`wp_terms` TO ‘read’;

GRANT SELECT ON `db`.`wp_term_relationships` TO ‘read’;

GRANT SELECT ON `db`.`wp_postmeta` TO ‘read’;

GRANT SELECT ON `db`.`wp_posts` TO ‘read’;

GRANT SELECT (user_activation_key, id, user_login, user_nicename, user_status, user_url, display_name, user_email, user_registered) ON `db`.`wp_users` TO ‘read’;

REVOKE ALL PRIVILEGES ON `db`.* from ‘user’;


With these commands, ‘user’ can only manipulate tables. If you’re an evil DBA, you can even revoke the “CREATE, DROP, ALTER” permission after install, and reactivate them only for upgrades or plugin installation. The ‘read’ user has the same permissions as before on wp_comments, has “SELECT” on all tables except the wp_users. For wp_users, we grant “SELECT” on all columns except the user_pass one.

Thanks to this configuration, even a SQL injection in a plugin will not reach the password hashes! We also removed dangerous permissions like “FILE”. I’d like to prevent timing attacks like “SELECT BENCHMARK(5000000,ENCODE(‘MSG’,’by 5 seconds’));” but i did not figure out what is the right syntax for this (I tried variations around: “revoke execute on function benchmark from read”, without result).

Thankfully, WordPress mostly works with this configuration, and I think that a lot of other applications could be protected like this. Imagine: you could grant insert but not select on the credit card table in an e-commerce application, and process transactions with a background task with the right permissions.

Database privileges are indeed a powerful tool to protect your code from SQL injections. They might require some architectural changes, but the profits can be huge for your security.