Information Security & US & Life & Love & Fashion & Hacking & Passion

Exporting data to a simple CSV file

While databases are a great tool to store and manage your data, you sometimes need to extract some of the data from your database to use it in another tool (a spreadsheet application being the most prominent example for this). In this recipe, we will show you how to utilize the respective MySQL commands for exporting data from a given table into a file that can easily be imported by other programs.

Getting ready

To step through this recipe, you will need a running MySQL database server and a working installation of a SQL client (like MySQL Query Browser or the mysql command line tool). You will also need to identify a suitable export target, which has to meet the following requirements:

• The MySQL server process must have write access to the target file

• The target file must not exist

In addition, a user with FILE privilege is needed (we will use an account named sample_install for the following steps; see also Chapter 8Creating an installation user)

Finally, we need some data to export. Throughout this recipe, we will assume that the data to export is stored in a table named table1 inside the database sample. As export target, we will use the file C:/target.csv (MySQL accepts slashes instead of backslashes in Windows path expressions). This is a file on the machine that runs the MySQL server instance, so in this example MySQL is assumed to be running on a Windows machine. To access the results from the client, you have to have access to the file (for example, using a file share or executing the MySQL client on the same machine as the server).

How to do it...

mysql> SELECT * FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';

There's more...
While the previous SELECT … INTO OUTFILE statement will work well in most cases, there are some circumstances in which you still might encounter problems. The following topics will show you how to handle some of those.
Handling errors if the target file already exists
If you try to execute the SELECT … INTO OUTFILE statement twice, an error File 'C:/target.csv' already exists occurs. This is due to a security feature in MySQL that makes sure that you cannot overwrite existing files using the SELECT … INTO OUTFILE statement. This makes perfect sense if you think about the consequences. If this were not the case, you could overwrite the MySQL data files using a simple SELECT because MySQL server needs write access to its data directories. As a result, you have to choose different target files for each export (or remove old files in advance).
Unfortunately, it is not possible to use a non-constant file name (like a variable) in the SELECT … INTO OUTFILE export statement. If you wish to use different file names, for example, with a time stamp as part of the file name, you have to construct the statement inside a variable value before executing it:
              Code View:              Scroll                /                Show All
mysql> SET @selInOutfileCmd := concat("SELECT * FROM sample.table1 INTO OUTFILE 'C:/target-", DATE_FORMAT(now(),'%Y-%m-%d_%H%i%s'), ".csv' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\r\n';");
mysql> PREPARE statement FROM @selInOutfileCmd;
mysql> EXECUTE statement;



					  


The first SET statement constructs a string, which contains a SELECT statement. While it is not allowed to use variables for statements directly, you can construct a string that contains a statement and use variables for this. With the next two lines, you prepare a statement from the string and execute it.
Handling NULL values
Without further handling, NULL values in the data you export using the previous statement would show up as"N in the resulting file. This combination is not recognized, for example, by Microsoft Excel, which breaks the file (for typical usage). To prevent this, you need to replace NULL entries by appropriate values. Assuming that the table sample.table1 consists of a numeric column a and a character column b, you should use the following statement:
              Code View:              Scroll                /                Show All
mysql> SELECT IFNULL(a, 0), IFNULL(b, "NULL") FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';



					  


The downside to this approach is that you have to list all fields in which a NULL value might occur.
Handling line breaks
If you try to export values that contain the same character combination used for line termination in the SELECT … INTO OUTFILE statement, MySQL will try to escape the character combination with the characters defined by the ESCAPED BY clause. However, this will not always work the way it is intended. You will typically define \r\n as the line separators. With this constellation, values that contain a simple line break \n will not cause problems, as they are exported without any conversion and can be imported to Microsoft Excel flawlessly. If your values happen to contain a combination of carriage return and line feed, the \r\n characters will be prepended with an escape character ("\r\n), but still the target file cannot be imported correctly. Therefore, you need to convert the full line breaks to simple line breaks:
              Code View:              Scroll                /                Show All
mysql> SELECT a, REPLACE(b, '\r\n', '\n') FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';



					  


With this statement, you will export only line breaks \n, which are typically accepted for import by other programs.
Including headers
For better understanding, you might want to include headers in your target file. You can do so by using a UNION construct:
Code View:              Scroll                /                Show All
mysql> (SELECT 'Column a', 'Column b') UNION ALL (SELECT * FROM sample.table1 INTO OUTFILE 'C:/target.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n');



					  


The resulting file will contain an additional first line with the given headers from the first SELECT clause.

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet    -> LINES TERMINATED BY '\r\n';

hint: mysql> LOAD DATA LOCAL INFILE '<dir>/pet.txt' INTO TABLE pet FIELDS terminated by '<delimiter>';

I used the full path name 'c:/<path to file>' and it worked fine, but my defaults aren't THE defaults. :-)

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')    -> OR (species = 'dog' AND sex = 'f');+-------+--------+---------+------+------------+-------+| name  | owner  | species | sex  | birth      | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  || Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |+-------+--------+---------+------+------------+-------+

mysql> SELECT owner FROM pet;+--------+| owner  |+--------+| Harold || Gwen   || Harold || Benny  || Diane  || Gwen   || Gwen   || Benny  || Diane  |+--------+
Notice that the query simply retrieves the          owner column from each record, and some of          them appear more than once. To minimize the output, retrieve          each unique output record just once by adding the keyword          DISTINCT:        
mysql> SELECT DISTINCT owner FROM pet;+--------+| owner  |+--------+| Benny  || Diane  || Gwen   || Harold |+--------+
mysql> SELECT name, birth FROM pet ORDER BY birth;+----------+------------+| name     | birth      |+----------+------------+| Buffy    | 1989-05-13 || Bowser   | 1989-08-31 || Fang     | 1990-08-27 || Fluffy   | 1993-02-04 || Claws    | 1994-03-17 || Slim     | 1996-04-29 || Whistler | 1997-12-09 || Chirpy   | 1998-09-11 || Puffball | 1999-03-30 |+----------+------------+
          On character type columns, sorting—like all other          comparison operations—is normally performed in a          case-insensitive fashion. This means that the order is          undefined for columns that are identical except for their          case. You can force a case-sensitive sort for a column by          using BINARY like so:          ORDER BY BINARY          col_name.        
          The default sort order is ascending, with smallest values          first. To sort in reverse (descending) order, add the          DESC keyword to the name of the column you          are sorting by:        
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;+----------+------------+| name     | birth      |+----------+------------+| Puffball | 1999-03-30 || Chirpy   | 1998-09-11 || Whistler | 1997-12-09 || Slim     | 1996-04-29 || Claws    | 1994-03-17 || Fluffy   | 1993-02-04 || Fang     | 1990-08-27 || Bowser   | 1989-08-31 || Buffy    | 1989-05-13 |+----------+------------+Query: SELECT numbers from table order by numbers

We have as result the following:

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

| numbers  | 

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

| 1        |

| 10       | 

| 20       |

| 30       |

| 50       |

| 500      |

| 1000     |

| 3000     |

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



We must make a Query, turning the numeric(integer) field to text and

ordered at this last one, Example:

Query: SELECT left(numbers, 11) as numbersSTR from table order by numbersSTR

We have as result the following:

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

| numbersSTR | 

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

| 1          |

| 10         | 

| 1000       |

| 20         |

| 30         |

| 3000       |

| 50         |

| 500        |

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

 
mysql> select number from (table) order by number;

+--------+

| number |

+--------+

| 1      |

| 10     |

| 2      |

| 3      |

| 4      |

| 5      |

| 6      |

| 7      |

| 8      |

| 9      |

+--------+



Use this:

mysql> select number from (table) order by (number+0);

+--------+

| number |

+--------+

| 1      |

| 2      |

| 3      |

| 4      |

| 5      |

| 6      |

| 7      |

| 8      |

| 9      |

| 10     |

+--------+

The (field + 0 ) converts the text/character in the field into an integer.

 
 

Download Snort and uncompress it.

Error messages you can get after the "./configure --with-mysql" command:

Build-essential is not installed

root@ubuntu:/home/po/Desktop/snort-2.6.1.3# ./configure --with-mysql
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for gawk... no
checking for mawk... mawk
checking whether make sets $(MAKE)... no
checking whether to enable maintainer-specific portions of Makefiles... no
checking for style of include used by make... none
checking for gcc... no
checking for cc... no
checking for cc... no
checking for cl... no
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details.

Libnet1-dev is not installed

ERROR! Libpcap library/headers not found, go get it from
http://www.tcpdump.org
or use the --with-libpcap-* options, if you have it installed
in unusual place

Libpcap0.8-dev is not installed

ERROR! Libpcap library/headers not found, go get it from
http://www.tcpdump.org
or use the --with-libpcap-* options, if you have it installed
in unusual place

Libpcre3-dev is not installed

ERROR! Libpcre header not found, go get it from
http://www.pcre.org

Libmysqlclient12-dev is not installed

**********************************************
ERROR: unable to find mysql headers (mysql.h)
checked in the following places
/usr/include
/usr/include/mysql
/usr/local/include
/usr/local/include/mysql
**********************************************

2 - CONFIGURE THE SQL DATABASE

Add a password for the MySQL root user:

3 - CONFIGURE SNORT FOR SQL

We now have to forward the logs into the MySql database:
This is already done by installing the snort-mysql package, we just need only to configure the username and password to access the snort database.
In the /etc/snort/snort.conf file, we have to change the line between (#DBSTART#) and (#DBEND#):