And the following query:
SELECT `;`.`*`.`.` FROM `;`.`*`;
is valid as well. So are the following:
DROP DATABASE IF EXISTS `;`; CREATE DATABASE `;`; CREATE TABLE `;`.`*` (`.` INT); CREATE TABLE `;`.```` (`.` INT); CREATE TABLE `;`.`$(ls)` (`.` INT);
Category: MySQL
MySQL related posts
Triggers Use Case Compilation, Part III
The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations.
Triggers Use Case Compilation, Part I
Triggers Use Case Compilation, Part II
Limitations and wishful features
Triggers are slow
The overhead of adding triggers is usually an even breaker. But I would like to believe speed will improve in time!
Triggers cannot act on the same table which activated them.
A thing I would like to do is have a rotating table. A log table is a perfect example: I only want to store logs up to 7 days back, or up to 1M rows. ON INSERT, (or once every 1000 inserts or so), I wish to remove oldest rows. This is not possible today since I can’t DELETE rows from the same table which caused the ON INSERT trigger to run. It can’t be hacked by calling on another table, then doing a circular trigger trick. MySQL will raise an error on run time, complaining about a loop.
Continue reading » “Triggers Use Case Compilation, Part III”
MySQL security: data integrity issues
MySQL’s security model is not as elaborate as other popular databases. It’s missing quite a lot.
I wish to point out what I think are some very disturbing security holes, which may affect the database integrity.
This post is not about Roles, Kerberos, IPs and such. It’s about simple MySQL features, which allow common, unprivileged users, to break data integrity by using unprotected session variables.
I will consider three such issues.
Triggers Use Case Compilation, Part II
In Triggers Use Case Compilation, Part I, I’ve demonstrated some triggers use scenarios.
We continue our examples of triggers usage.
Counters and aggregations bookkeeping
Consider the City table: each city belongs to a certain country. Some questions we may be interested in are:
- How many cities are there per country?
- What’s the sum of cities population per country?
- What’s the population of the largest city per country?
Answering any of these questions is an easy SQL excercise. But aggregation is required, and full table scan (or full index scan, if we’re lucky) is essentially part of any execution plan. What if we can’t pay the price for these queries? What if we need immediate, or near immediate response?
sql_mode: a suggestion
I thought this deserves more than a comment on my previous post on the subject, in which I expressed the opinion that sql_mode is undesired.
Back to reality: sql_mode is here right now. What else can be done?
Suggestion
Is there anything to do about sql_mode? I believe so: make it strict by default.
Do we need sql_mode?
sql_mode must be one of the most elusive issues in setting up a MySQL database. It is also responsible for difficulties in migration, upgrading and securing of databases.
MySQL usually has little issues with migrating database from one machine to another, one version to another, one OS to another, one architecture to another. If we’re not sure, we can always migrate using mysqldump’s logical dump, right?
Not entirely right. The elusive sql_mode (empty by default) may affect out backup+restore, may affect our application behavior, may affect our data integrity. Continue reading » “Do we need sql_mode?”
Triggers Use Case Compilation, Part I
I’ve run by quite a few triggers lately on production systems. In previous posts, I’ve written about problems solved with triggers. So here’s a compilation of some solutions based on triggers; and some problems which are not (yet?) solvable due to current triggers limitations.
Triggers can be used to:
- Maintain integrity
- Enhance security
- Enhance logging
- Assist with archiving
- Restrict table size
- Manage caching
- Manage counters
Triggers are not fast. In fact, they can add quite an overhead if misused. Some of the triggers presented here are known to work on real life production systems, though, and work well. But make sure you benchmark before embarking on extensive application changes. Continue reading » “Triggers Use Case Compilation, Part I”
Using triggers to block malicious code: an example
Web applications face constant exploitation attempts. Those with a user base must keep their users’ private data, well… private.
While the MySQL security model allows restricting users access to databases, tables and even columns, it has no built in feature for restricting the rows access within the given table.
One cannot allow a user to only update rows 0 through 99, but restrict that user from updating rows 100 to 199. Such restrictions are usually managed in the application level, by adding a necessary “… AND filtering_column = some_value…”
Many web application have the notion of an ‘admin’ account, or several such accounts, which provide greater control over the application. The ‘admin’ account is one account to which many attacks are targeted. One such attack is an attempt to modify the admin’s password, such that the attacker can later log in with and access restricted data. Continue reading » “Using triggers to block malicious code: an example”
REPLACE INTO: think twice
The REPLACE [INTO] syntax allows us to INSERT a row into a table, except that if a UNIQUE KEY (including PRIMARY KEY) violation occurs, the old row is deleted prior to the new INSERT, hence no violation.
Sounds very attractive, and has a nice syntax as well: the same syntax as a normal INSERT INTO’s. It certainly has a nicer syntax than INSERT INTO … ON DUPLICATE KEY UPDATE, and it’s certainly shorter than using a SELECT to see if a row exists, then doing either INSERT or UPDATE.
But weak hearted people as myself should be aware of the following: it is a heavyweight solution. It may be just what you were looking for in terms of ease of use, but the fact is that on duplicate keys, a DELETE and INSERT are performed, and this calls for a closer look. Continue reading » “REPLACE INTO: think twice”
Using memcached functions for MySQL; an automated alternative to Query Cache
There’s a lot of buzz around memcached. memcached is widely used, and has clients for many programming languages and platforms. TangentOrg have developed a memcached client in the form of MySQL UDFs (User Defined Functions).
I wish to discuss the memcached functions for MySQL: if and how they should be used.
Disclaimer: I do not work with memcached functions for MySQL on a production system; all that is written here reflects my opinion on how things should be done.
With memcached functions for MySQL, we can do the following:
SELECT memc_set('mykey', 'The answer is 42');
SELECT memc_get('mykey');
(See my previous post on how to install memcached functions for MySQL).
In what scenario should we use these functions?
I believe memcached is the right tool for the application level. I am less enthusiastic about using it from MySQL. Sure, pushing it down to MySQL centralizes everything. Instead of having all my application code (PHP, Java etc.) access memcached separately, they can all access one single MySQL node, which gets to access memcached. I see two problems with this approach: Continue reading » “Using memcached functions for MySQL; an automated alternative to Query Cache”