#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    17
    Rep Power
    0

    Php to export to csv over ssl wtf?!


    Ok, I have tried all day to get this to work. The best I have got to is that from page A, the user clicks download, which acts as a prompt to pass 'sqlcode' a variable holding a mysql query that was submitted by the user from selecting some values from drop down boxes, which have populated a html table - to page B that is supposed to take the query and results and spit out a csv file:
    Code:
    <?php require_once('Connections/Connection1.php'); ?>
    <?php
    //create query to select as data from your table
    $select = $_GET['sqlcode'];
    
    //run mysql query and then count number of fields
    $export = mysql_query ( $select ) 
    or die ( "Sql error : " . mysql_error( ) );
    $fields = mysql_num_fields ( $export );
    
    //create csv header row, to contain table headers 
    //with database field names
    for ( $i = 0; $i < $fields; $i++ ) {
    $header .= mysql_field_name( $export , $i ) . ",";
    }
    
    //this is where most of the work is done. 
    //Loop through the query results, and create 
    //a row for each
    while( $row = mysql_fetch_row( $export ) ) {
    $line = '';
    //for each field in the row
    foreach( $row as $value ) {
    //if null, create blank field
    if ( ( !isset( $value ) ) || ( $value == "" ) ){
    $value = ",";
    }
    //else, assign field value to our data
    else {
    $value = str_replace( '"' , '""' , $value );
    $value = '"' . $value . '"' . ",";
    }
    //add this field value to our row
    $line .= $value;
    }
    //trim whitespace from each row
    $data .= trim( $line ) . "\n";
    }
    //remove all carriage returns from the data
    $data = str_replace( "\r\n", "", $data);
    
    
    //create a file and send to browser for user to download
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private",false);
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment; filename=\"excelfile.csv\";" );
    header("Content-Transfer-Encoding: binary");
    
    print "$header\n$data";
    exit;
    ?>
    However, what I get is IE(v8) seeing it as a HTML file and displaying all the text in the browser window....any ideas??
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,508
    Rep Power
    594
    1) First please enclose your code in [ PHP ] tags. See the sticky at the top of this forum that says READ THIS BEFORE POSTING.
    2)Your description indicates that PHP is not installed or not enabled on your server. Check your httpd.conf file and make sure these lines exist and or are uncommented:
    Code:
    LoadModule php5_module modules/libphp5.so
    AddType application/x-httpd-php .php
    3) Under no circumstance should you be using the code you posted. For one thing the MySQL extensions are deprecated. Second and most important, your code is wide open to SQL injection. Switch to PDO and use prepared statements. In addition should be validating the input.

    Comments on this post

    • Jacques1 disagrees : *lol* The PDO thingy really is his least problem. He takes the query directly through the URL.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Man, is this some kind of suicide cult? Do you understand the risks that come with executing arbitrary SQL queries somebody somewhere sent you?

    I mean, I've gotten familiar with "developers" not escaping user input before dumping it into all kinds of queries. But letting your visitors write their own queries takes it to a whole new level. Who needs SQL injections? mdemetri2 lets you hand-craft your own queries and will then happily execute them on the server. What comes next? Taking shell commands through the URL?

    Nobody -- I repeat: nobody -- except database administrators are supposed to write their own SQL queries. If you let your end-users send you queries, your application is deeply, deeply broken and needs to be fixed right now. Don't worry about that little CSV issue. You have bigger problems. I hope that site isn't online yet, because if it is, I'm sure it has already become a member of some Chinese botnet attacking US companies.

    Yes, this really is a WTF.

    If you're so keen on getting "hacked", at least use your own PC. A server with a high bandwidth running berzerk isn't funny.

    Comments on this post

    • PaulGer agrees
    Last edited by Jacques1; May 9th, 2013 at 07:01 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,003
    Rep Power
    376
    maybe this thing he is doing is just for admin (giving him the benefit of the doubt!)
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,508
    Rep Power
    594
    With proper validation and prepared statements, I don't think it matters that the query is passed through the URL. Unless of course, there is sensitive data in the query but that should be a given. Even a novice should know better than that. It should at least be encrypted.

    Comments on this post

    • PaulGer disagrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    What? How do you validate a whole query? How do prepared statements help you with a complete query?

    He takes a complete SQL query from the URL and simply executes it. You know, something like SELECT price FROM products. But what if the user doesn't play nice? How about

    Code:
    DROP TABLE my_super_important_table_without_any_backup;
    Or maybe

    Code:
    SELECT '<?php system($_GET["cmd"]);' INTO OUTFILE '/var/www/my_own_shell.php';
    It doesn't even take a user with bad intentions. Any external attacker could simply create a URL with a query in it and then plant it on the user -- so all your encryption, authentication and whatnot won't help you.

    And, no, this was not supposed to be some phpmyadmin clone. The OP said that the query is generated from the user selecting options from a dropdown menu.

    The whole approach is very, very wrong. If that stuff doesn't worry you, you need to get your senses fixed. mdemetri2 has basically written his own database rootkit.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,508
    Rep Power
    594
    In my case, on the rare occasion that I've done it, the query content is very strict. I simply check that it has the correct format and values. If it is anything other than "select something from table', for example, it is rejected.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by gw1500se
    If it is anything other than "select something from table', for example, it is rejected.
    So you're fine with SELECT User, Password from mysql.user?

    Of course you can limit the privileges, parse the query, apply a whitelist/blacklist and whatnot. But if the user can only select specific information anyway, why even let them pass an arbitrary query, which you then strip down with a lot of effort and a big risk of overlooking something? Why not start with the specific information in the first place?

    Anyway, this is way off topic. Of course a professional database administrator might sometimes need to access a database interface while being connected over a VPN -- just like policemen sometimes have to shoot people if their own life is in danger. That doesn't mean that sending queries or shooting people is a normal procedure. The professionals that actually need to send queries would hardly ask us for our opinion in this forum.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,508
    Rep Power
    594
    Nope. That does not meet the criteria.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo