Securing custom MySQL tables in Wordpress
January 8, 2016 3:42 AM   Subscribe

I have personal tables I'm using within the Wordpress database for my own data storage and manipulation purposes. How can I protect these *most easily* from sql injections and/or whatever other evil lurks?

I have a personal Wordpress site which I use for standard WP site-type purposes but also contains some pages I use for data entry into custom tables which I have created which live in the same database. (So: single database, the one that comes with the installation, plus the additional tables I use to store my data.)

I (and only I) can insert data into those tables via the pages. I can also update records through those pages.

I want that information to be read-only to anyone who isn't me.

What's the *easiest* and most reliable way to do this? I'm oriented toward a working product here, for my own use, and not looking to have NSA-level security but do want to do it 'right'--so if there is a continuum in methodology I would like to set the difficulty level to "normal/medium".
posted by A Terrible Llama to Computers & Internet (5 answers total) 1 user marked this as a favorite
You don't need any other product, really. All you need to do is to make sure that the MySQL user used to access the WP tables doesn't have permission to access your 'personal' tables. You can do this through phpMyAdmin or via the command-line. The pages you use to update your 'personal' tables would need to log into MySQL using a different user with permissions for those tables.
posted by pipeski at 4:13 AM on January 8, 2016

Are you concerned about other people who *already* have access to the database messing with your tables, or some outside attacker hacking into your database and getting your custom tables along with everything else?

If it's the former, you need to create a different login for yourself, and configure the privileges appropriately (GRANTs in SQL terminology) so the other users only have SELECT privileges for your custom tables.

If it's the later, it's not really the tables you have to worry about - it's the pages you use for data entry - that's where the bugs that allow things like SQL injection live. What pages are these, and who set them up/ maintains them? Security doesn't really come as an add-on product for things like that, and in general is hard: standard advice is to avoid rolling your own at all costs, and try to rely on things that are known to work and have somebody maintaining them.

If you built these pages within Wordpress (I'm not really familiar with it), you may already be set - in the sense that you already have whatever level of security Wordpress provides, but you probably want to have someone familiar with the inner workings of WP look them over.

If you wrote them from scratch, you need to do some reengineering: Pick a simple framework (I've used CodeIgniter for stuff like that, but many are floating around) that will handle stuff like login, sessions, database connections, SQL sanitizing etc for you and rewrite your pages trying to use ready-made components as much as possible. This will get you 80% of the way there, in that you will at least avoid the rookie mistakes everybody who doesn't do security for a living makes.

Note that a different login will also give you some additional security in the second case, but only if it's more difficult for the attacker to steal your personal db login info than hacking your WP installation.
posted by Dr Dracator at 4:14 AM on January 8, 2016 [1 favorite]

it's not really the tables you have to worry about - it's the pages you use for data entry - that's where the bugs that allow things like SQL injection live. What pages are these, and who set them up/ maintains them?

Right -- they're my pages, I wrote them, they're for my custom tables in the DB. Right now I'm either managing it by not making the page viewable to the outside world or by saying something to the effect of 'if you're not atl_admin, you can't do this thing' but there is something about this that I remember reading that is not optimal or not fully secure. (I'm about to start working on this again but haven't looked at it for a while -- I remembered I was doing something generally not accepted as the Right Way and being unable to understand what was wrong with the fairly obvious way I was doing it.)

It's also possible that I've got a basic misunderstanding of how to do this: I think I might be querying and showing results on the same page as opposed to displaying results in a new page.
posted by A Terrible Llama at 8:34 AM on January 8, 2016

Regarding SQL injection: if you've written the code yourself, the thing you want to ensure is that any SQL you submit to the database uses prepared statements with bind parameters instead of concatenating data (such as the values of an INSERT statement or operands in WHERE clause predicates) into the query string. Any dynamic part controllable by the user can be considered to be up for SQL Injection, so if table or column names in the query are set via the pages you've built, they will have to be checked against a white list of values to avoid their manipulation by those with malicious intent.

SQL injection is only possible if the attacker has access to the page, obviously, but even if it's protected by some sort of authorization it's a good idea to guard against. If the authorization process for the page is custom and queries the database, make sure it uses prepared statements! I've seen username/password input fields in the wild that were SQL injectable.

This may be a bit past easy/medium, but the The Open Web Application Security Project (OWASP) has a good overview of what SQL Injection is. They also have a good top ten list of vulnerabilities in web application security. You'll want some knowledge of what is possible to check how any framework, plugin, or security solution attempts to address the issue. SQL injection is the sort of attack has the potential to give an attacker access to all database data and to the database server itself.
posted by Mister Cheese at 8:31 PM on January 8, 2016

According to at least one source, given the way I've been doing this, I'm over thinking it:

Both the $wpdb->insert() and the $wpdb->update() methods perform all the necessary sanitization for writing to the database.

And if one wanted to overdo it, there is a Prepare statement.

I think I was overly focused on securing the database instead of focusing on securing the queries.
posted by A Terrible Llama at 10:05 AM on March 24, 2016

« Older Remember..... you will die.   |   Plain white sneakers for wide feet Newer »
This thread is closed to new comments.