Guymon

MySQL Query Profiler

In einem Artikel über profiling MySQL auf Xaprb habe ich gerade den MySQL Query Profiler gefunden.

mysql-query-profiler is a program to read a file containing one or more SQL statements, execute them, and analyze the output of SHOW STATUS afterwards. It then prints statistics about how the batch performed. For example, it can show how many table scans the batch caused, how many page reads, how many temporary tables, and so forth. It can print information about the entire batch (default), every query, or only specified queries. It has three levels of verbosity (default is level 1, not very verbose).

Google for SQL Injection Vulnerabilities

Michael Sutton zeigt wie sich Google für die Suche nach SQL Injection Vulnerabilities nutzen läßt.

After some contemplating, I came up with the following theory:

• Identify a population of web sites likely to have databases and determine the request syntax.
  • For this requirement, I turned to Google and took a bit of a shortcut. (…) For my purposes, I chose inurl:”id=10”. This query was selected for two reasons. First, using a GET request would allow me to leverage a search engine to identify a population of URLs for testing. Second, a URL containing a query such as “id=10” is likely to be querying a database for something such as a product catalog.(…)
• Submit an altered query in order to elicit a SQL error message.
  • Once again, I took a relatively simplistic approach here by altering the query to inject an single quote ahead of the actual query. (…) Injecting an extra quote will create a query with an open quotation mark and this will often cause the application to return an error message. I took this one step further and URL encoded the single quote to bypass sites that may be filtering for unusual characters. Therefore, the injected query was now “id=%2710”.
• Parse all responses to look for signs of verbose SQL error messages.

• After much trial and error, I settled on three simple words that allowed for the identification of most SQL error messages. I would grep responses for “sql”, “query” and “error”.

via Bruce Schneier

MySQL Optimizing for Query Speed

Bei devshed ist aus dem Buch MySQL Certification Guide ein Kapitel über MySQL Optimizing for Query Speed erschienen.

This chapter discusses general principles that are useful for optimizing queries to run more efficiently. It covers the following optimization strategies:

• The primary optimization technique for reducing lookup times is to create good indexes. This is true not just for retrievals (SELECT statements); indexing reduces row lookup time for UPDATE and DELETE statements as well. You should know general principles for creating useful indexes and for avoiding unnecessary ones.
• The EXPLAIN statement provides information about how the MySQL optimizer processes queries. This is of value when you’re trying to determine how to make a query run better (for example, if you suspect indexes are not being used as you think they should be).
• The way a query is written might prevent indexes from being used even if they are available. Rewriting the query often will allow the optimizer to use an index and process a query faster. Other times you can use query modifiers to give the scheduler a hint about how to execute a query.
• In some cases, query processing for a task can be improved by using a different database design. This includes techniques such as choosing a storage engine with properties that best match application requirements and using summary tables.

MySQL 5 - federated engine

Giuseppe Maxia beschreibt seine Erfahrungen mit der federated engine in MySQL 5.

One of the most exciting features introduced in MySQL 5 is the federated engine. The ability to access data from a remote server without the constraints of replication tickles every programmer’s fancy.

Unfortunately, as of today, the documentation is not quite as detailed as I would like. I have the feeling that the federated engine has been somehow neglected. This article comes from my personal experience with this engine.

MySQL Security

Auf devshed gibt’s ein Auszug aus dem MySQL 5.0 Certification Guide.

• Basic security procedures, both for the filesystem and for the initial MySQL accounts. These procedures prevent users with login accounts on the server host from attacking MySQL directories or files. They also prevent clients from connecting to the MySQL server without a password.
• How to set up MySQL accounts. This includes a discussion of the types of privileges available, and how the GRANT and REVOKE statements work.
• How the server uses the information in the grant tables to manage access control when clients connect.