Independent Digital

Security Issues with LOAD DATA LOCAL

The LOAD DATA statement can load a file located on the server host, or a file located on the client host when the LOCAL keyword is specified.

There are two security issues when using the LOCAL version of LOAD DATA:

• The MySQL server initiates the transfer of the file from the client host to the server host. It is therefore possible for the server to access any file on the client host to which the client user has read access.

• In a Web environment, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to.

These problems have been address in the later versions of MySQL (versions 3.23.49 and 4.0.2):

• All MySQL clients and libraries in binary distributions are compiled, by default, with the –enable-local-infile option, to be compatible with MySQL 3.23.48 and before.

• If you build MySQL from source, you must invoke configure with the –enable-local-infile option, or else LOAD DATA LOCAL cannot be used by any client unless it is written explicitly to invoke mysql options(?MYSQL OPT LOCAL INFILE, 0),

• Disable all LOAD DATA LOCAL commands from the server side by starting mysqld with the –local-infile=0 option.

• With the mysql command-line client, LOAD DATA LOCAL can be enabled by specifying the –local-infile[=1] option and disabled with the –local-infile=0 option. For mysqlimport, the –local or -L option enables local data file loading.

• If you use LOAD DATA LOCAL in programs that read the [client] group from option files, you can add the local-infile=1 option to that group. But use the loose- prefix to keep this from causing problems for programs that do not understand local-infile. If LOAD DATA LOCAL INFILE is disabled then a client that issues such a statement receives an error message.

Want to be notified when new posts are published?

Your email:

 

Security-Related mysqld Options

The following mysqld options affect security:

–allow-suspicious-udfs

This controls whether user-defined functions that have only an symbol for the main function can be loaded. By default, the option is off and only UDFs that have at least one auxiliary symbol can be loaded

If you start the server with –local-infile=0, clients cannot use LOCAL in LOAD DATA statements

Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs.

–safe-show-database

–safe-user-create

If this option is enabled, a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql user table or any column in the table.

–secure-auth

Disallow authentication for accounts that have old (pre-4.1) passwords.

–secure-file-priv=

This option limits the effect of the LOAD_FILE() function and the LOAD DATA and SELECT … INTO OUTFILE statements to work only with files in the specified directory.

–skip-grant-tables

This option causes the server not to use the privilege system at all. This gives anyone with access to the server unrestricted access to all databases.

–skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.

–skip-networking

Do not allow TCP/IP connections over the network. All connections to mysqld must be made via Unix socket files.

–skip-show-database

With this option, the SHOW DATABASES statement is allowed only to users who have the SHOW DATABASES privilege, and the statement displays all database names.

Want to be notified when new posts are published?

Your email:

 

Securing MySQL Against Attackers

The password is encrypted when you connect to the MySQL server and as of MySQL 4.1.1 is very secure. However, it is advisable to upgrade if you are still using an earlier version of MySQL as hackers could quite easily crack your password. For those using MySQL Enterprise, the Enterprise Monitor enforces best practices for the maximum security of the MySQL server.

Besides the password, all other information is not encrypted and is transferred as text and can be read by hackers. Use the compressed protocol if you are concerned about this as it makes it more difficult to hack. You can also use MySQL’s internal SSL support to make the connection even more secure or use SSH to get an encrypted TCP/IP connection between a MySQL server and a MySQL client. (See http://www.openssh.org/ for an Open Source SSH client).

Consider the following in order to improve your MySQL security:

• Require all MySQL accounts to have a password.

• Never run the MySQL server as the Unix root user – mysqld should be run as an ordinary, unprivileged user.

• Do not allow the use of symlinks to tables

• Make sure that the only Unix user with read or write privileges in the database directories is the user that mysqld runs as.

• Do not grant the PROCESS or SUPER privilege to non-administrative users.

• Do not grant the FILE privilege to non-administrative users.

• Use IP numbers rather than hostnames in the grant tables.

• Restrict the number of connections allowed to a single account – by setting the max_user_connections variable in mysqld…

Want to be notified when a new post is published?

Your email:

 

If you have a MySQL database that is open to the internet then you need to read the following security guidelines so that you will be in a better position to protect your data.All security measures should apply to the entire server and not just the MySQL server in order to protect it from any and all types of attacks, including:

? Eavesdropping

? Altering

? Playback

? Denial of service.

MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations. MySQL also supports SSL-encrypted connections between MySQL clients and servers.

When using MySQL:

? Don?t give anyone (except MySQL root accounts) access to the user table in the mysql database!

? Familiarize yourself with the MySQL access privilege system (GRANT and REVOKE)

o Don?t grant more privileges than necessary.

o Don?t grant privileges to all hosts.

Check the following:

? Try mysql -u root. If you are able to connect to the server without being asked for a password, then anyone can connect to your MySQL database!

? Use SHOW GRANTS to check which accounts have access to what. Use the REVOKE statement to remove those privileges that are not necessary.

? Don?t store any plain-text passwords in your database. Instead, use a hashing function such as, MD5()or SHA1() and store the hash value.

? Don?t choose passwords from dictionaries. Read up on how to choose a secure password.

? Do use a firewall. Firewalls are able to keep out about 50% of all types of exploits. Put MySQL behind the firewall.

o Check:

Try to scan your ports from the Internet using a tool such as nmap. MySQL?s default port should not be accessible from untrusted hosts. Also try the following command from some remote machine, where server_host is the hostname or IP number of the host on which your MySQL server runs:

shell> telnet server_host 3306

If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router. If telnet hangs or the connection is refused then the port is, correctly, blocked.

? Do not trust any data entered by users of your applications. They can enter special or escaped character sequences in Web forms, URLs, etc, which can then access your database.

Protect both string data values as well numeric data. Use single quotes around the numeric constants:

SELECT * FROM table WHERE ID='234'

If the user enters extra information, it all becomes part of the string, In a numeric context, MySQL converts this string into a number and strips any trailing non-numeric characters from it.

? Also protect publicly available information against denial of service attacks. Otherwise, your server becomes unresponsive to legitimate users.

Check:

o Use single and double quote marks (?'? and ?"?) in all of your Web forms.

o Modify dynamic URLs by adding %22 (?"?), %23 (?#?), and %27 (?'?) to them.

o Modify data types in dynamic URLs from numeric to character types using the above characters.

o Do not pass unchecked values to MySQL. Test enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error.

o Check the size of data before passing it to MySQL.

o Use a different username (and not your administrative username) for your application to connect to the database.

o Do not give your applications any access privileges they do not need.

o Use application programmes such as Php, Pearl, etc to escape special characters in data values. This prevents users from entering values that cause the application to generate statements that have a different effect than you intend.

? Do not transmit plain (unencrypted) data over the Internet. Rather use an encrypted protocol such as SSL or SSH..

? Use the tcpdump and strings utilities to check whether MySQL data streams are unencrypted by issuing a command like the following (works under Linux and probably with some modification under other systems):

shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings

Be aware though that the lack of plain text does not always mean that the information is encrypted. Consult a security expert if you need high security.

Want to be notified when new posts are published?

Your email:

 

You want to install Apache, MySQL and PhP but are put off by the hassle of setting them all up. Don’t worry, here’s a simple way of doing it. Just download a file, run it to install, follow the instructions and everything is done for you. No settings to change, no nothing. Couldn’t be simpler.

WampServer is a Windows web development environment. It allows you to create web applications with Apache, PHP and the MySQL database. It also comes with PHPMyAdmin and SQLiteManager to easily manage your databases.

WampServer installs automatically (installer), and its usage is very intuitive. You will be able to tune your server without even touching the setting files.

WampServer is the only packaged solution that will allow you to reproduce your production server. Once WampServer is installed, you have the possibility to add as many Apache, MySQL and PHP releases as you want.

You can download it here. Her’s the WampServer homepage.

Here’s a quick and easy step-by-step tutorial of how to install it

Your email:

 

The MyISAM storage engine (see article on MyISAM storage engine) is one of the available storage engines on the MySQL server. It is the default storage engine and is always available however, you can switch between the different storage engines. Some storage engines may not be available on a particular server. It is therefore best to first check whether or not your preferred engine is available before designing your database. Simply use the SHOW ENGINES statement in the query browser. This will list the available storage engines.



Configuring the MyISAM engine

the server operating system’s filesystem determines the caching of data rows read from or written to a MyISAM table

The MyISAM engine manages its own key cache for indexing




The operation of the key cache

initially it is empty

when the index needs to be read, it checks the key cache.If the index exists in the cache, the in-memory values are read.If it does not exist, it reads the table’s index file to get the values which are then put into the cache.

if the cache is full, the cache buffer that has been used the least for the longest time is discarded.

if the chosen buffer has not been modified, it is overwritten. If it has been modified, it is written to the index file before being overwritten.

The key cache’s main purpose is to reduce disk access. (See article on MySQL indexes) So the more values found in the cache, the greater the performance as values not found in the cache have to be accessed from the disk.

Frequently used values stay in the key cache and reduces the need to make room for new values by discarding unused buffers. The larger the key cache, the greater the “hits” or found values. This minimises disk access. A good configuration change is therefore to increase the size of your index if you have the available memory. Do this by setting the key_buffer_size system variable in the option file. The default is 8MB but can be set to 4GB, naturally depending on your available memory. You need to balance the available memory with demands made by other resources on your server that require memory.

For example, to set the key cache to 1GB do this:

[mysqld]
key_buffer_size = 1G




Multiple key caches

As of MySQL 4.1.1 it is possible to have multiple key caches. The revised caching mechanism has the following features:

multiple key caches possible

total cache size, cache block size and buffer discard algorithm are controllable

tables can be assigned to specific caches

table indexes can be preloaded into a cache

You can set up a number of key caches which can be allocated to separate tables. These tables will then not all be using the same key cache at the same time.

Each key cache is associated with a set of system variables (in this format cache_name.var_name) having these components:

key_buffer_size – total size of key cache in bytes

key_cache_block_size – size of blocks in key cache, in bytes

key_cache_division_limit – influences the cache buffer reuse algorithm and is set between 1 to 100

key_cache_age_threshold – how long buffers stay unused before being made available to be overwritten. Default is 300 and minimum is 100

One key cache is the default and is used if a key cache component is referred to without using a cache name.

You can set the key cache components at startup or runtime. For example:

[mysqld]
my_cache.key_buffer_size = 12M

will create a cache named my_cache with a 12MB buffer size on startup. This statement will create the same size cache at runtime:

SET GLOBAL my_cache.key_buffer_size = 12*1024*1024;

(Key caches are GLOBAL; 1024 is the number of bytes)

You can assign MyISAM tables to key caches with CACHE INDEX. For example:

CACHE INDEX myDbase.table1, myDbase.table2 IN my_cache;

which assigns table1 and table2 from myDbase to the cache named my_cache.

You can also preload table indexes into a cache with LOAD INDEX INTO CACHE. For example:

LOAD INDEX INTO CACHE table1, table2;

You can destroy a cache by setting its size to zero. Tables assigned to this cache are then reassigned to the default cache.

Key cache assignments are destroyed when the server shuts down. You can have these assigned automatically when the server starts by placing them in a file. You then start the server with an –init-file option that names the file.

FREE at last! An easy to use (and FREE) MySQL syntax reference guide. Ever wanted to create a database, alter or analyze a table but didn’t know how to write the query statement? Sure you can consult the manual but that is sometimes quite time consuming. Here at last is an easy to use online reference guide. Select what it is that you want to do from the drop-down menu, press go and a detailed description of the correct syntax to use is displayed. Quick and simple, no more wading through unnecessary jargon. Go to the free online MySQL syntax reference guide now!

It can, at a first glance, be difficult to understand the MySQL syntax, especially for those new to programming. Here is a brief summary of the conventions used in the MySQL manual. Use it when you first refer to the MySQL manual. It should help make interpreting the syntax easier.

Text in this style is used for

• SQL statements
• database, table, and column names
• program listings and source code
• environment variables
  • Example: ?To reload the grant tables, use the FLUSH PRIVILEGES statement.?

Text in this style

• indicates the names of executable programs and scripts
  • examples being mysql and mysqld

Text in this style is used for

• variable input for which you should substitute a value of your own choosing.
  • Example: ALTER {DATABASE | SCHEMA} [db_name] alter_specification [alter_specification] …
    • here you can optionally include the db_name but have to include at least one alter_specification
    • you have to include either DATABASE or SCHEMA (schema is synonymous with database)

Filenames and directory names are written like this:

• ?The global my.cnf file is located in the /etc directory.?

To specify a wildcard, use the ?%? character.?

Text in this style is used for emphasis

Text in this style is used in

• table headings
• and to convey especially strong emphasis.

shell>

• indicates a command that you execute from your login shell
  • Example: shell> type a shell command here

mysql>

• indicates a statement that you execute from the mysql client program
  • Example: mysql> type a mysql statement here

The ?shell? is your command interpreter.

• On Unix, this is typically a program such as sh, csh, or bash.
• On Windows, the equivalent program is command.com or cmd.exe, typically run in a console window.

When you enter a command or statement shown in an example, do not type the prompt (mysql> or shell>) shown in the example.

Database, table, and column names must often be substituted into statements.

• the manual uses db_name, tbl_name, and col_name.
  • Example: mysql> SELECT col_name FROM db_name.tbl_name;
    • Here you need to supply your own column name, database name and table name

SQL keywords are not case sensitive and may be written in any lettercase. The manual uses UPPERCASE.

Square brackets (?[? and ?]?) indicate optional words or clauses.

• Example DROP TABLE [IF EXISTS] tbl_name
  • IF EXISTS is optional and the statement is also valid if written like this:
    • DROP TABLE tbl_name

When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (?|?).

• If the choice is optional then the alternatives are listed within square brackets (?[? and ?]?):
  • Example: TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
    • Here this statement could be written like this, ommiting all the optional alternatives:
      • TRIM (str)

• If a choice must be made, the alternatives are listed within braces (?{? and ?}?):
  • Example: {DESCRIBE | DESC} tbl_name [col_name | wild]
    • Here a choice must be made, so either of these statements is valid:
      • DESCRIBE tbl_name
      • DESC tbl_name
        • you can include either col_name or wild as these are optional

An ellipsis (...) indicates the omission of a section of a statement

• to provide a shorter version of more complex syntax.
  • Example, INSERT ... SELECT is shorthand for the form of INSERT statement that is followed by a SELECT statement.
• also to indicate that the preceding syntax element of a statement may be repeated.
  • Example, multiple reset_option values may be given, with each of those after the first preceded by commas:
    • RESET reset_option [,reset_option] …
      • here it is optional to repeat the reset_option after the ellipsis
        

MySQL Query Browser

MySQL Query Browser is a visual tool for creating, executing, and optimizing SQL queries for your MySQL Database Server. It gives you a complete set of drag-and-drop tools to visually build, analyze and manage your queries. It also provides:

Query Toolbar to create and execute queries and navigate query history

Script Editor gives you control to manually create or edit SQL statements

Results Window so you can compare and work with multiple queries

Object Browser enables you to manage your databases, bookmarks, and history using a Web Browser like interface

Database Explorer enables you to select tables and fields to query, as well as create and delete tables

Table Editor allows you to create, modify and delete tables

Inline Help gives you instant help access to selected objects, parameters, and functions

Intuitive Easy to Use Interface

MySQL Query Browser provides a Web-browser like interface giving you instant access to all of the Query Browser’s functionality. The main query window is customizable and allows you to view or hide individual toolbars. Navigation buttons allow you to browse through your query history so that you can review and re-execute a previous query.

Visual Tools to Rapidly Build Queries

The MySQL Query Browser allows you to build your own queries. You can use the visual tools or you can code your own query.

Easily Manage Multiple Queries using the Results Window

You can compare multiple queries by viewing them on individual tabs or by splitting them either vertically or horizontally in the Results Window. Queries in different parts of a split result area can be joined together for master-detail analysis.

Manage Databases Using the Object Browser

The Object Browser allows you to manage your databases, query bookmarks, and query history.

You can use the Object Browser to:

Choose which database and tables to query

Add commonly-used queries to a collection of bookmarks

Browse through previously issued queries to use them again

The database explorer is a file explorer type interface allowing you to select tables and fields to query, choose a table to edit, create new tables and databases, and drop tables and databases.

Visually Create and Modify Tables

The MySQL Table Editor allows you to visually create and modify tables as well as table’s column and index information. You can also create FOREIGN KEY relationships and specify the storage engine and the table’s default character set.

Easily Create, Edit, and Debug SQL Statements

The Script Editor is an interface for creating, editing, and debugging large SQL scripts involving multiple SQL statements. The Script Area features line numbering and syntax highlighting making it easier to work with scripts.

Download the MySQL Query Browser now for FREE!

Download the MySQL Query Browser NOW!

MySQL Administrator

MySQL Administrator is a visual administration console that enables you to administer your MySQL environment. It integrates database management and maintenance into a single environment, with an intuitive graphical user interface.

MySQL Administrator enables you to perform all the command line operations visually.

These include:

configuring servers

administering users

monitoring database health

monitoring replication status

backup and restore

viewing logs

User Administration in Minutes

MySQL Administrator makes it very easy to administer users, to grant privileges and view user privilege information.

Single View Dynamic Health Monitoring

Dynamic health monitoring displays the key indicators of the health of your MySQL environment. Dynamic graphs enable you to see percentage of memory usage, connection usage, hit rates, as well as the number of SQL queries on an ongoing basis.

Quickly Optimize MySQL

MySQL Administrator allows you to view and setup key parameters of your database so that you can quickly optimize its performance.

Rapid Disaster Prevention and Recovery

MySQL Administrator allows you to visually select the schemas and tables you want to backup/restore from your hierarchical data directory. Creating the backup or restoring from a backup is then very easy.

Server Information At-a-Glance

MySQL Administrator allows you to quickly view basic information about the server and client. It enables you to view items such as server status, client and server version numbers and IP address, as well as connection information.

View Replication Status

MySQL Administrator allows you to easily view the network topology of your replication master and slaves.

View Server Logs from a Centralized Administration Environment

You can view all the MySQL log files including error logs, binary logs, and InnoDB logs from a single, centralized administration.

Service Control

MySQL Administrator gives you complete control of service creation. Including the starting and stopping of the MySQL server.

Browse Catalog

MySQL Administrator enables you to browse your catalog and view corresponding tables with columns, all indices and users with privileges to access selected databases. You can also monitor and optimize tables from here.

Cross Platform

MySQL Administrator is available for Microsoft Windows, Linux and Mac OS X.

Download MySQL Administrator for FREE!

You can download MySQL Administrator here.