Recently we've received an alert from one of our clients that running threads are high on one of their servers. Once we logged in, we noticed that all the selects were waiting for table level read lock. We scrolled through the process list, and found the selects which were causing the problems. After killing it, everything went back to normal. At first we couldn’t understand why the query took so long, as it looked like all the others. Then we noticed, that one of the WHERE clauses was strange. There, we found a SLEEP(3) attached with OR to the query. Obviously, this server was the victim of a SQL injection attack.
What is SQL injection?
I think most of us know what SQL injection is, but as a refresher, SQL injection is when someone provides malicious input into WHERE, to run their own statements as well. Typically this occurs when you ask a user for input, like username, but instead of a real name they give you a MySQL statement that will be run by your server without you knowing it.![Exploits of a Mom](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/exploits_of_a_mom-1.png?width=666&height=205&name=exploits_of_a_mom-1.png)
- explicit lock table
- insert/update/delete on MyISAM
- ALTER table on InnoDB
How can we defend ourselves from SQL injection?
There are several ways to secure yourself from SQL injection.- First of all, validate the input. If you expect only letters and numbers, filter it with regexp for example, to make sure there are no special characters there. Also escape the inputs on application side; programming languages have built-in function to do that (eg.: mysql_real_escape_string() in PHP)
- Use prepared statement! It won’t allow 2 clause if you specified only 1. When you use prepared statements, the variables are transmitted as MySQL variables. Even if the string is not escaped, it will end up in one variable, and MySQL treats is as a longer string. (For more details see: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html )
- Use a tool like MySQL Enterprise Firewall, which is a plugin for MySQL and can filter your statements to make sure there are no things like: || 1==1
Share this
Previous story
← Oracle's CREATE INDEX command can take a hint
You May Also Like
These Related Stories
Anthos: How Google changed the perception of hybrid cloud solutions
Anthos: How Google changed the perception of hybrid cloud solutions
Jan 29, 2020
3
min read
MySQL high availability with ProxySQL, Consul and Orchestrator
MySQL high availability with ProxySQL, Consul and Orchestrator
Nov 18, 2019
6
min read
Guide to Cassandra Thread Pools
Guide to Cassandra Thread Pools
May 11, 2014
9
min read
No Comments Yet
Let us know what you think