Mysql – Which MySQL queries are causing this error message

First you need to do is run this query:

SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';

This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:

• Run CHANGE MASTER TO for controlling replication coordinates
• KILL or mysqladmin kill to kill threads belonging to other accounts
• PURGE BINARY LOGS to systemically delete binary logs
• Make configuration changes using SET GLOBAL to modify global system variables
• mysqladmin debug command
• enabling or disabling logging
• performing updates even if the *read_only* system variable is enabled
• starting and stopping replication on slave servers
• specification of any account in the DEFINER attribute of stored programs and views
• HERE IS THE MOST IMPORTANT ONE FOR YOUR PROBLEM: : Enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

You will need to login as root@localhost and revoke SUPER privilege as follows:

UPDATE mysql.user SET super_priv='N'
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
FLUSH PRIVILEGES;

Once you do this, whenever all users flood mysql connections, only root@localhost can login. After all, if everybody and his grandmother had SUPER privilege, this would bar root@localhost from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:

mysql> SET GLOBAL max_connections = 300;

That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.

CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf

[mysqld]
max_connections=300

You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:

cd
wget mysqltuner.pl
perl mysqltuner.pl

The 3rd line under Performance Metrics has this

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 20h 46m 22s (8M q [10.711 qps], 129K conn, TX: 90B, RX: 19B)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 2.1G global + 5.4M per thread (2000 max threads)
[OK] Maximum possible memory usage: 12.6G (80% of installed RAM)

See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.

In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.

It may, yes, though it is difficult to tell without knowing what the code is doing around that point. Anywhere where you take user input and place it into ad-doc SQL an easy injection route is presented to a malicious user simply by adding ' characters into the relevant input.

For instance if your code does something like:

results = dbObject.RunSQL("SELECT * FROM some_object WHERE person_name='" & request("name") & "'")

a malicious user can make a malformed HTTP request with the name value containing '; DELETE * FROM users_table; SELECT ' the code you present to the database becomes:

SELECT * FROM some_object WHERE person_name=''; DELETE * FROM users_table; SELECT ''

Perfectly we formed SQL so it will run, and if you have a table called users_table you have just lost its contents. There are much more clever attacks than this simple one possible by the same route. This isn't limited to SELECT statements - any ad-hoc SQL is potentially vulnerable to this.

Without seeing some of the relevant code, what seems to be happening is some ad-hoc SQL is being put together with a string that contains apostrophe characters resulting in incorrect syntax. So you likely have a bug that creates an injection attack route, though in this instance the bug is simply causing an error doe to some specific input value. Find it and fix it while the force is with you! Instigate a full code review too, there may be many more examples of the problem.

To fix this sort of error you need to look at two things:

• Proper input validation. Refuse any request where a value seems to be in the wrong format. Expecting a UUID in a given variable? Then error and do nothing if you see anything that isn't a valid representation of that. Expecting a positive integer? Make sure the value contains nothing but numeric characters and throw out the request if not. Do this as early as possible, definitely before trying to touch your database with anything using values received from the client (in fact you should validate data from the database too before passing it back - bad data may have got into the database earlier either maliciously or due to a bug).
• Use parametrised queries where ever possible - avoid ad-hoc SQL except where absolutely necessary. Your data layer (ADO, OLEDB, ...) should handle this cleanly for you meaning you do not need to worry about it misinterpreting characters that have special meaning, for instance apostrophes sometimes found in names like O'hare. For bulk inserts/updates where single procedure calls per row are too inefficient consider using an updatable recordset rather than ad-hoc INSERTstatements if such a concept is supported.
• If you absolutely must put together ad-hoc SQL strings, be very very careful to escape special characters.

One thing I would recommend, though it can be costly, is having an instance of your application (not one containing real client data of course) properly penetration tested by a professional pen-testing company. The cost and hassle of this is nothing compared to losing all your data and/or customers if a hole they could have found is instead found and abused by a black-hat.