Friday Jul 13, 2012

Performance Schema in version 5.6 helps to prevent SQL Injection attacks

There were few prominent SQL Injection attacks lately.



Such breakages could be possible when an attacker finds that user data, sent to a SQL server, not properly sanitized.

Usually successful attack is preceded by queries which cause parse errors.


mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/er'ror;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ror' at line 1

Easiest test to find out if website can be attacked is to send, using web form or address bar, a value which contains special symbol, say "'", like in the word "D'Artagnan", then analyse if it was properly handled.



There are practices which web developer should use to avoid such situations. They include 100% sanitizing of all incoming data, allowing only expected values and rejecting any other or limitation of database accounts which web site uses. In latter case even if SQL injection happens, damage, which it can make, would be minimal.

But all these means work at the design stage. What to do if you are responsible for large legacy project, so you can not be 100% sure everything is handled properly?

In old time we could only suggest to log queries into general query log, then search for those which look suspicious. This hard job can be hardly automated: general query log does not contain information about query return code, therefore you have to guess which query could cause parse error yourself and not much automation can be applied.

In version 5.6 MySQL introduced new tables in Performance Schema database.
Performance schema in 5.6 became really user friendly and I am falling in love with it.
A bunch of them, events_statements_*, can be useful for debugging possible SQL injection attacks.

event_statements_* tables keep queries, which were executed in past, together with information about how they were proceeded. It includes:



  • time, spent waiting for table locks

  • how many temporary tables were created, splitting disk and memory-based tables

  • which optimizer techniques were chosen

  • statistics based on them.



However, for SQL injection discussion, we are interested in diagnostic information, such as return SQLSTATE code, warning and error information.

These fields can give the list of queries which were used by attackers to predict their chances to break the website.

Performance schema has two special error-handling instruments: statement/com/Error for statements which were parsed successfully, but rejected thereafter and statement/sql/error for statements which were rejected at parsing state.

In case of SQL Injection we should examine those statements, which were rejected at parsing state: statement/sql/error. They can easily found with help of this query:



mysql> select thread_id, sql_text from events_statements_history_long where event_name='statement/sql/error';

+-----------+-------------------------------------------------------------------------------------+

| thread_id | sql_text                                                                            |

+-----------+-------------------------------------------------------------------------------------+

|        16 | select * from events_statements_history_long wher event_name='statement/sql/error'  |

|        16 | select * from events_statements_history_long here event_name='statement/sql/error'  |

|        16 | select * from events_statements_history_long where event_name='statement/sql/er'ror |

+-----------+-------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)


In this example I specially made errors in queries I typed, but the idea can be derived. Usually application does not produce such queries at all: they all should be fixed at the development stage, therefore periodically checking for such queries can be good practice for determining possibility of SQL injection attacks.

And to finish this topic I should mention another tool which can help to prevent SQL injection attacks even before they reach real database. This is Oracle Database Firewall. I'll copy part of its white paper here:

Oracle Database Firewall monitors data access, enforces access policies, highlights anomalies and helps protect against network based attacks originating from outside or inside the organization. Attacks based on SQL injection can be blocked by comparing SQL against the approved white list of application SQL. Oracle Database Firewall is unique and offers organizations a first line of defense, protecting databases from threats and helping meet regulatory compliance requirement.

With its unique feature of blocking access by analyzing all incoming SQL statements your database can be really protected. And amazingly it supports MySQL out of the box.

References:

MySQL user manual - http://dev.mysql.com/doc/refman/5.6/en/performance-schema-statements-tables.html
Mark Leith's blog - http://www.markleith.co.uk/ps_helper/#statements_with_errors_or_warnings
Oracle Database Firewall - http://www.oracle.com/technetwork/products/database-firewall/overview/index.html

Wednesday Jul 04, 2012

Warning and error information in stored procedures revisited

Originally way to handle warnings and errors in MySQL stored routine was designed as follows:


  • if warning was generated during stored routine execution which has a handler for such a warning/error, MySQL remembered the handler, ignored the warning and continued execution

  • after routine is executed MySQL checked if there is a remembered handler and activated if any

This logic was not ideal and causes several problems, particularly:


  • it was not possible to choose right handler for an instruction which generated several warnings or errors, because only first one was chosen

  • handling conditions in current scope messed with conditions in different

  • there were no generated warning/errors in Diagnostic Area that is against SQL Standard.

First try to fix this was done in version 5.5. Patch left Diagnostic Area intact after stored routine execution, but cleared it in the beginning of each statement which can generate warnings or to work with tables. Diagnostic Area checked after stored routine execution.

This patch solved issue with order of condition handlers, but lead to new issues. Most popular was that outer stored routine could see warnings which should be already handled by handler inside inner stored routine, although latest has handler. I even had to wrote a blog post about it.

And now I am happy to announce this behaviour changed third time.

Since version 5.6 Diagnostic Area cleared after instruction leaves its handler.

This lead to that only one handler will see condition it is supposed to proceed and in proper order. All past problems are solved.

I am happy that my old blog post describing weird behaviour in version 5.5 is not true any more.

Friday Mar 23, 2012

Story of success: MySQL Enterprise Backup (MEB) was successfully integrated with IBM Tivoli Storage Manager (TSM) via System Backup to Tape (SBT) interface.

Since version 3.6 MEB supports backups to tape through the SBT interface.

The officially supported tool for such backups to tape is Oracle Secure Backup (OSB).

But there are a lot of other Storage Managers. MEB allows to use them through the SBT interface. Since version 3.7 it also has option --sbt-environment which allows to pass environment variables, not needed by OSB, to third-party managers. At the same time MEB can not guarantee it would work with all of them.

This month we were contacted by a customer who wanted to use IBM Tivoli Storage Manager (TSM) with MEB. We could only say them same thing I wrote in previous paragraph: this solution is supposed to work, but you have to be pioneers of this technology. And they agreed.

They agreed to be the pioneers and so the story begins.

MEB requires following options to be specified by those who want to connect it to SBT interface:

--sbt-database-name: a name which should be handed over to SBT interface. This can be any name. Default, MySQL, works for most cases, so user is not required to specify this option.
--sbt-lib-path: path to SBT library. For TSM this library comes with "Data Protection for Oracle", which, in its turn, interfaces with Oracle Recovery Manager (RMAN), which uses SBT interface. So you need to install it even if you don't use Oracle.
--sbt-environment: environment for third-party manager. This option is not needed when you use OSB, but almost always necessary for third-party SBT managers. TSM requires variable TDPO_OPTFILE to be set and point to the TSM configuration file.
--backup-image=sbt:: path to the image. Prefix "sbt:" indicates that image should be sent through SBT interface

So full command in our case would look like:

./mysqlbackup --port=3307 --protocol=tcp --user=backup_user --password=foobar \
--backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt"
--backup-dir=/path/to/my/dir backup-to-image
And this command results in the following output log:

MySQL Enterprise Backup version 3.7.1 [2012/02/16]
Copyright (c) 2003, 2012, Oracle and/or its affiliates. All Rights Reserved.

INFO: Starting with following command line ... 
 ./mysqlbackup --port=3307 --protocol=tcp --user=backup_user
        --password=foobar --backup-image=sbt:my-first-backup
        --sbt-lib-path=/usr/lib/libobk.so
        --sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt"
        --backup-dir=/path/to/my/dir backup-to-image

sbt-environment: 'TDPO_OPTFILE=/path/to/my/tdpo.opt'
INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
            At the end of a successful 'backup-to-image' run mysqlbackup
            prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir                          =  /path/to/data
  innodb_data_home_dir             =  /path/to/data
  innodb_data_file_path            =  ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M
  innodb_log_group_home_dir        =  /path/to/data
  innodb_log_files_in_group        =  2
  innodb_log_file_size             =  268435456

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir                          =  /path/to/my/dir/datadir
  innodb_data_home_dir             =  /path/to/my/dir/datadir
  innodb_data_file_path            =  ibdata1:2048M;ibdata2:2048M;ibdata3:64M:autoextend:max:2048M
  innodb_log_group_home_dir        =  /path/to/my/dir/datadir
  innodb_log_files_in_group        =  2
  innodb_log_file_size             =  268435456

Backup Image Path= sbt:my-first-backup
mysqlbackup: INFO: Unique generated backup id for this is 13297406400663200
120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS is 'Data Protection for Oracle: version 5.5.1.0'
120220 08:54:00 mysqlbackup: INFO: meb_sbt_session_open: MMS version '5.5.1.0'
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: System tablespace file format is Antelope.
mysqlbackup: INFO: Found checkpoint at lsn 31668381.
mysqlbackup: INFO: Starting log scan from lsn 31668224.
120220  8:54:00 mysqlbackup: INFO: Copying log...
120220  8:54:00 mysqlbackup: INFO: Log copied, lsn 31668381.
          We wait 1 second before starting copying the data files...
120220  8:54:01 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata1 (Antelope file format).
mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000
120220  8:55:30 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata2 (Antelope file format).
mysqlbackup: Progress in MB: 200 400 600 800 1000 1200 1400 1600 1800 2000
120220  8:57:18 mysqlbackup: INFO: Copying /path/to/ibdata/ibdata3 (Antelope file format).
mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
120220 08:57:22 mysqlbackup: INFO: Starting to lock all the tables....
120220 08:57:22 mysqlbackup: INFO: All tables are locked and flushed to disk
mysqlbackup: INFO: Opening backup source directory '/path/to/data/'
120220 08:57:22 mysqlbackup: INFO: Starting to backup all files in subdirectories of '/path/to/data/'
mysqlbackup: INFO: Backing up the database directory 'mysql'
mysqlbackup: INFO: Backing up the database directory 'test'
mysqlbackup: INFO: Copying innodb data and logs during final stage ...
mysqlbackup: INFO: A copied database page was modified at 31668381.
          (This is the highest lsn found on page)
          Scanned log up to lsn 31670396.
          Was able to parse the log up to lsn 31670396.
          Maximum page number for a log record 328
120220 08:57:23 mysqlbackup: INFO: All tables unlocked
mysqlbackup: INFO: All MySQL tables were locked for 0.000 seconds
120220 08:59:01 mysqlbackup: INFO: meb_sbt_backup_close: blocks: 4162  size: 1048576  bytes: 4363985063
120220  8:59:01 mysqlbackup: INFO: Full backup completed!
mysqlbackup: INFO: MySQL binlog position: filename bin_mysql.001453, position 2105
mysqlbackup: WARNING: backup-image already closed
mysqlbackup: INFO: Backup image created successfully.:
           Image Path: 'sbt:my-first-backup'

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 31668224
   End LSN                    : 31670396
-------------------------------------------------------------

mysqlbackup completed OK!
Backup successfully completed.

To restore it you should use same commands like you do for any other MEB image, but need to provide sbt* options as well:

$./mysqlbackup --backup-image=sbt:my-first-backup --sbt-lib-path=/usr/lib/libobk.so \
--sbt-environment="TDPO_OPTFILE=/path/to/my/tdpo.opt" --backup-dir=/path/to/my/dir image-to-backup-dir
Then apply log as usual:

$./mysqlbackup --backup-dir=/path/to/my/dir apply-log
Then stop mysqld and finally copy-back:

$./mysqlbackup --defaults-file=path/to/my.cnf --backup-dir=/path/to/my/dir copy-back

 

Disclaimer. This is only story of one success which can be useful for someone else. MEB is not regularly tested and not guaranteed to work with IBM TSM or any other third-party storage manager.

Thursday Apr 28, 2011

InnoDB Recovery forces

I just answered in yet another bug report where user experiences problem because OS crash damaged InnoDB tablespace. I wonder why users don't use InnoDB recovery. Looks like I need to create an entry in Domas-style:


IF YOUR TABLESPACE


IS CORRUPTED


TRY


FORCING INNODB RECOVERY


http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Warning and error information in stored procedures

UPD: Information in this blog entry is not true since version 5.6. Please read revisited blog about warning and error information in stored procedures.


I got this question second time this week, so I think it is worth writing a blog post about this topic.
Question was "Why I get error in EVENT which calls a STORED PROCEDURE which already has HANDLER?"
This changed in version 5.5 after fix of Bug #36185, Bug #5889, Bug #9857 and Bug #23032. Explanation is in this commit:


  The problem was in the way warnings/errors during stored routine execution
were handled. Prior to this patch the logic was as follows:
- when a warning/an error happens: if we're executing a stored routine,
and there is a handler for that warning/error, remember the handler,
ignore the warning/error and continue execution.
- after a stored routine instruction is executed: check for a remembered
handler and activate one (if any).
This logic caused several problems:
- if one instruction generates several warnings (errors) it's impossible
to choose the right handler -- a handler for the first generated
condition was chosen and remembered for activation.
- mess with handling conditions in scopes different from the current one.
- not putting generated warnings/errors into Warning Info (Diagnostic
Area) is against The Standard.
The patch changes the logic as follows:
- Diagnostic Area is cleared on the beginning of each statement that
either is able to generate warnings, or is able to work with tables.
- at the end of a stored routine instruction, Diagnostic Area is left
intact.
- Diagnostic Area is checked after each stored routine instruction. If
an instruction generates several condition, it's now possible to take a
look at all of them and determine an appropriate handler.

This means you will notice error which was last handled in stored routine after its execution.
In case of EVENT error would be written to the error log file.


Assume you have a table and two stored procedures:


create table t1(f1 int);
insert into t1 values (1), (2), (3), (4), (5);
\\d |
create procedure search_t1()
begin
declare done int default 0;
declare val int;
declare cur cursor for select f1 from t1;
declare continue handler for not found set done=1;
open cur;
repeat
fetch cur into val;
if not done then
select val;
end if;
until done end repeat;
select done;
end
|
create procedure call_search()
begin
call search_t1();
end
|

If you call call_search you will get a warning from search_t1:


mysql> \\W
Show warnings enabled.
mysql> call call_search()|
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.33 sec)
...
Query OK, 0 rows affected, 1 warning (0.33 sec)
Error (Code 1329): No data - zero rows fetched, selected, or processed

This happens because Diagnostic Area now is not cleared after stored procedure execution. One of advantages of this fix, in addition to what it is compatible with SQL Standard now, is you can handle errors from stored routines called inside another routine.
Imagine you have same 2 routines, but defined as follow:


create procedure search_t1()
begin
    declare val int;
    declare cur cursor for select f1 from t1;
   
    open cur;
    repeat
        fetch cur into val;
        select val;
    until 0=1 end repeat;
end
|
create procedure call_search()
begin
    declare nf int default 0;
    declare continue handler for not found set nf=1;
    call search_t1();
    select nf;
end
|

Before version 5.5 call of call_search would fail:


mysql> call call_search()|
+------+
| val |

+------+
| 1 |
+------+
1 row in set (0.01 sec)
...
+------+
| val |
+------+
| 5 |
+------+
1 row in set (0.01 sec)

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed


Pay attention no query after call of search_t1 was executed. Since version 5.5 same call would not fail:


mysql> call call_search();
+------+
| val  |
+------+
|    1 |
+------+
1 row in set (0.12 sec)
...
+------+
| val  |
+------+
|    5 |
+------+
1 row in set (0.12 sec)

+------+
| nf   |
+------+
|    1 |
+------+
1 row in set (0.12 sec)

Query OK, 0 rows affected, 1 warning (0.12 sec)


Of course this is just generic example and actually shows bad code practice, but real life can provide us not so generic errors

Wednesday Apr 20, 2011

From Collaborate 11: Diagram of actions which should be performed when error found.

This year I was speaking at Collaborate 11 about actions one need to perform when find her SQL application behaves wrongly.


New thing which was not presented in my earlier notes is simple how-to of how to deal with replication errors. You can find this in second presentation.


Slides are here: part 1 and part 2.


I also created PDF diagram which can be print and used as visual aid for cases when you meet an error. You can download it here or at the Conference website here (see file for session 410).


If you want to print it you should either use A3 sheet with 50% scale or A1 sheet with 100% scale.

Tuesday Mar 22, 2011

Spring saving time: watch your TIMESTAMPs

Every March we have a lot of bug reports and support issues about Daylight Saving Time changes.


General rule here is to remember 1 hour at March, 27 night will be missed in most of European countries: for example in Moscow after 01:59:59 next time will be 03:00:00. And yes, I am too late for America =)


MySQL handles such timestamps properly, therefore expect no such time and date will be inserted in STRICT mode and next valid date will be inserted for not existent time in forgiving mode. For Moscow '2011-03-27 03:00:00' will be inserted if you try to insert timestamps in '2011-03-27 02:00:01' - '2011-03-27 02:59:59' range.


You can see how Europe switch to DST at http://www.timeanddate.com/news/time/europe-starts-dst-2011.html Please note some countries such as Turkey have special rules for year 2011.


If you use timezones with special rules for this year make sure your operating system zoneinfo information is up to date, then reload MySQL time_zone\* tables using mysql_tzinfo_to_sql utility and finally restart MySQL server. You can read about mysql_tzinfo_to_sql at http://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html



Have a nice spring!


Thursday Oct 21, 2010

Translation of "Methods for searching errors in SQL application" just finished

Translation of "Methods for searching errors in SQL application" just finished, epilogue is at http://sql-error.microbecal.com/en/concl.html


It contains list of methods which had been discussed.




Epilogue



Finally I'd like to repeat methods which we discussed. Unfortunately
there are several problems left. I will be glad to know your opinion
about what else to descuss. I will be waiting your notes at sveta_dot_smirnova_at_oracle_dot_com or sveta_at_js-client_dot_com



List of methods.


...


Rest of the text is here

Wednesday Oct 13, 2010

Translation of "Appendix. Methods of copying and moving of MySQL databases." of "Methods for searching errors in SQL application" just published

Translation of appendix about methods of copying and moving MySQL databases just published. This is just short overview of possible methods and does not pretend to be detailed guide. It starts as:




Appendix. Methods of copying and moving of MySQL databases.



In this application I'd like to shortly discuss general methods of backup and moving of mySQL databases.



Easier and recommended way of data moving is mysqldump utility. You can copy data with help of following command:





$mysqldump dbname [tblname ...] >dump.sql


...


and continues here


Saturday Sep 25, 2010

Translation of Summary of Part 4 of "Methods for searching errors in SQL application" just published


Translation of summary of last part, "Techniques, used for debugging of Production applications" just published. This is almost end, only appendix about backup techniques and epilogue left.




Summary.



In the last chapter we discussed methods of testing problems which can happen only on production server. Lets repeat them:


Method #25: if something unexpected happens check error log first.


Method #26: turn InnoDB Monitor to on to have information about all InnoDB transactions in the error log file.


Method #27: use slow query log to find all slow queries.


Method #28: use MySQL Sandbox for fast and convenient testing of your application using several versions of MySQL server.


Method #29: use part of data when work with queries which return wrong results from huge tables.


Original text is here

Tuesday Sep 14, 2010

Translation of "Chapter 11. Techniques, used for debugging of Production applications" of "Methods for searching errors in SQL application" just published.


English translation of first and only chapter of last part 4 "Techniques, used for debugging of Production applications" just published.



Chapter 11. Techniques, used for debugging of Production applications.



Unfortunately is not always possible to find error while testing. Often they only happen when high load.


How do you know about such problems?


One of most important sources of information about problems is error
log file. There you can find information such as server crashes,
connection errors (if option log-warnings=2 turned on), about options
which were specified in the configuration file, but had not turned on
because error and some others. There is a rule for working with error
log file: if something unexpected happens check error log first. Error
log file also contains information about server errors which are not
accessible for clients. Therefore is better to have it always turned on
even if you do logging at application level.


...


Rest of the chapter is here.


Wednesday Sep 01, 2010

Translation of Summary of Part 3 of "Methods for searching errors in SQL application" just published

Not much new this time: just summary of part 3 published and fixed mistake in chapter 10 (thanks, Shane!).

Summary.

In the third part we discussed methods of application debugging in cases when query plays secondary role in the problem.

I'd like to bring your attention we only discussed most frequent cases while MySQL server has a lot of parameters which of them can affect application. Analyze parameters which you use. One of the methods is run problematic query using MySQL server running with option --no-defaults and examine if results are different for MySQL server run with parameter which you use. If results are different analyze why parameter affects it and solve the problem.

...

Rest of the chapter is here.

Wednesday Aug 11, 2010

Translation of "Chapter 10. Lost connection to MySQL server during query." of "Methods for searching errors in SQL application" just published.

This chapter is about possible reasons of "Lost connection to MySQL server" error not discussed in previous one.



Chapter 10. Lost connection to MySQL server during query



You can see error "Lost connection to MySQL server" not only because
too small connect_timeout, but because other reasons too. In this
chapter we discuss these reasons.




$php phpconf2009_4.php

string(44) "Lost connection to MySQL server during query"


Most likely error log will show what happened:


...


Rest of the chapter is here


Tuesday Aug 03, 2010

Translation of "Chapter 9. Misterious vanishing of the server." of "Methods for searching errors in SQL application" just published

In this chapter we discuss timeouts which can lead to "MySQL server has gone away" and "Lost connection to MySQL server" errors.


 Extract here is short, because there are a lot of code quotes which I can not properly format here.




Chapter 9. Misterious vanishing of the server.



Often this looks like:




$php phpconf2009_3.php

string(26) "MySQL server has gone away"


Code:


...


Rest of the chapter is here

Wednesday Jul 21, 2010

Translation of "Chapter 8. Large amount of data." of "Methods for searching errors in SQL application" just published

I started translation of Part 3 "Other cases". In this part I mostly describe how wrong settings can lead to misterious errors. First chapter in this part about large amount of data.



Part 3. Other cases.



Chapter 8. Large amount of data.



There are cases when query is just symptom of wrong behavior, but true reason is wrong settings.



One of the cases is too small max_allowed_packet for data sent. MySQL
server variable max_allowed_packet defines maximum possible amount of
data which MySQL server can receive or send. Amount of
max_allowed_packet is specified in bytes.



Error usually looks like:





$mysql51 test <phpconf2009_1.sql

ERROR 1153 (08S01) at line 33: Got a packet bigger than 'max_allowed_packet' bytes


...


Rest of the chapter is here


About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« August 2015
SunMonTueWedThuFriSat
      
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
     
Today