Looking through our exception tracker the other day, I ran across a notice from our slow-query logger that caught my eye. I saw a SELECT … WHERE … LIKE query with lots of percent signs in the LIKE clause. It was pretty obvious that this term was user-provided and my first thought was SQL injection.

[3.92 sec] SELECT ... WHERE (profiles.email LIKE '%64%68%6f%6d%65%73@%67%6d%61%69%6c.%63%6f%6d%') LIMIT 10

Looking at the code, it turned out that we were using a user-provided term directly in the LIKE clause without any checks for metacharacters that are interpreted in this context (%, _, \).

def self.search(term, options = {})
  limit = (options[:limit] || 30).to_i
  friends = options[:friends] || []
  with_orgs = options[:with_orgs].nil? ? false : options[:with_orgs]

  if term.to_s.index("@")
    users = User.includes(:profile) …

Looking through our exception tracker the other day, I ran across a notice from our slow-query logger that caught my eye. I saw a SELECT … WHERE … LIKE query with lots of percent signs in the LIKE clause. It was pretty obvious that this term was user-provided and my first thought was SQL injection.

[3.92 sec] SELECT ... WHERE (profiles.email LIKE '%64%68%6f%6d%65%73@%67%6d%61%69%6c.%63%6f%6d%') LIMIT 10

Looking at the code, it turned out that we were using a user-provided term directly in the LIKE clause without any checks for metacharacters that are interpreted in this context (%, _, \).

def self.search(term, options = {})
  limit = (options[:limit] || 30).to_i
  friends = options[:friends] || []
  with_orgs = options[:with_orgs].nil? ? false : options[:with_orgs]

  if term.to_s.index("@")
    users = User.includes(:profile) …

English: Madrid MySQL Users Group will be holding their next meeting on Tuesday, 10th November at 19:30h at the offices of Tuenti in Madrid. David Fernández will be offering a presentation “MySQL Automation @ FB”.  If you’re in Madrid and are interested please come along. We have not been able to give much advance notice so if … Continue reading MMUG14: MySQL Automation at Facebook

The post MMUG14: MySQL Automation at Facebook first appeared on Simon J Mudd's Blog.

MySQL 5.7 came out with support for JSON, improved geometry, and virtual columns. Here's an example showing them all playing together.

Download citylots.json.

It comes as one big object, so we'll break it up into separate lines:
grep "^{ .type" citylots.json > properties.json

Connect to a 5.7 instance of MySQL.

CREATE TABLE citylots (id serial, j json, p geometry as (ST_GeomFromGeoJSON(j, 2)));
LOAD DATA LOCAL INFILE 'properties.json' INTO TABLE citylots (j);

A few of the rows don't contain useful data:
DELETE FROM citylots WHERE j->'$.geometry.type' IS NULL;

In …

Upgrading MySQL

NOTE: This blog is an updated version of the previously published blog, Upgrading Directly From MySQL 5.0 to 5.6 With mysqldump, modified for upgrading to 5.7.

Upgrading MySQL is a task that is almost inevitable if you have been managing a MySQL installation for any length of time.…

Recently, I happened to have an onsite engagement and the goal of the engagement was to move a database service to RDS Aurora. Like probably most of you, I knew the service by name but I couldn’t say much about it, so, I Googled, I listened to talks and I read about it. Now that my onsite engagement is over, here’s my first impression of Aurora.

First, let’s describe the service itself. It is part of RDS and, at first glance, very similar to a regular RDS instance. In order to setup an Aurora instance, you go to the RDS console and you either launch a new instance choosing Aurora as type or you create a snapshot of a RDS 5.6 instance and migrate it to Aurora. While with a regular MySQL RDS instance you can create slaves, with Aurora you can add reader nodes to an existing cluster. An Aurora cluster minimally consists of a …

Many of you know I publish a newsletter monthly. One thing I love about it is that after almost a decade of writing it regularly, the list has grown considerably. And I’m always surprised at how many former colleagues are actually reading it. So that is a really gratifying thing. Thanks to those who are, … Continue reading Should we be muddying the relational waters? Use cases for MySQL & Mongodb → …

This Log Buffer Edition covers the weekly blog posts of Oracle, SQL Server and MySQL.

Oracle:

• An Index or Disaster, You Choose (It’s The End Of The World As We Know It).
• SQL Monitoring in Oracle Database 12c.
• RMAN Full Backup vs. Level 0 Incremental.
• Auto optimizer stats after CTAS or direct loads in #Oracle 12c.
• How to move …

When tackling a new enterprise project to support a given business, you face the challenge of choosing and committing to a database platform. The choice should be the one most adequate, given the needs and requirements of the new information system and data to be hosted and managed. Typically, a number of factors should be taken into consideration like security features, storage requirements,

We are yet to see a software that runs perfectly, without any issues. MySQL is no exception there. It’s not the software’s fault - we need to be clear about that. We use MySQL in different places, on different hardware and within different environments. It’s also highly configurable. All those features make it great product but they come with a price - sometimes some settings won’t work correctly under certain conditions. It is also pretty easy to make simple human mistakes like typos in the MySQL configuration. Luckily, MySQL provides us with means to understand what is wrong through the error log. In this blog, we’ll see how to read the information in the error log.

This is the fifteenth installment in the ‘Become a MySQL DBA’ blog series. Our previous posts in the DBA series include:

• …