Archive | Kippo RSS feed for this section

export MySQL query to csv | php | kippo password db

12 Jan

I thought it a good idea to generate a user/pass database based on my kippo installation seeing as it is a target for multiple bruteforce databases from multiple attackers. Below is the SQL Statement i use to generate the data.

SELECT DISTINCT username, password from auth where password <> "" ORDER BY username ASC

Now, I wanted a way to get this via my website and present it as a download to those interested. Tried a few code examples via google and couldn’t get the majority of them to work. After some digging and fiddling I eventually managed to get my kippo data out into .csv file which is automatically sent back to the web session and presented to the client for download.

<?php
  //
  // establish database connection
  //
  $conn = mysql_connect( 'MYSQL_HOST', 'MYSQL_USERNAME', 'MYSQL_PASSWORD' ) or die( mysql_error( ) );
  mysql_select_db( 'MYSQL_DATABASE', $conn ) or die( mysql_error( $conn ) );
  //
  // execute sql query
  //
  $query = sprintf( 'SELECT * FROM MYSQL_TABLE' );
  $result = mysql_query( $query, $conn ) or die( mysql_error( $conn ) );
  //
  // send response headers to the browser
  // following headers instruct the browser to treat the data as a csv file called export.csv
  //
  header( 'Content-Type: text/csv' );
  header( 'Content-Disposition: attachment;filename=export.csv' );
  //
  // output header row (if atleast one row exists)
  //
  $row = mysql_fetch_assoc( $result );
  if ( $row )
  {
    echocsv( array_keys( $row ) );
  }
  //
  // output data rows (if atleast one row exists)
  //
  while ( $row )
  {
    echocsv( $row );
    $row = mysql_fetch_assoc( $result );
  }
  //
  // echocsv function
  //
  // echo the input array as csv data maintaining consistency with most CSV implementations
  // * uses double-quotes as enclosure when necessary
  // * uses double double-quotes to escape double-quotes 
  // * uses CRLF as a line separator
  //
  function echocsv( $fields )
  {
    $separator = '';
    foreach ( $fields as $field )
    {
      if ( preg_match( '/\\r|\\n|,|"/', $field ) )
      {
        $field = '"' . str_replace( '"', '""', $field ) . '"';
      }
      echo $separator . $field;
      $separator = ',';
    }
    echo "\r\n";
  }
?>
Advertisements