Query with bind parameters, exported results to CSV 1

[Katerine]

Hi,
I have what I hope is an easy PHP question. I'm fairly new to PHP, though not new to programming in general (I'm used to VB, though).

I have a search form that, when submitted, should run a search query and export the results to CSV, and I'm running into problems. I haven't found anything online about how to run mysqli_query() on a query with parameters (I need it to not leave itself wide open to SQL Insertion). The closest I've come is the following code (simplified; for details about what's getting passed, see the results of the debug statement):

function export_query_to_csv($mysqli, $prep_stmt, $searchtext, $searchparam)
{
//	$result = $mysqli->query($prep_stmt);
	$stmt = $mysqli->prepare($prep_stmt);

	if ($stmt) {
		if ($searchtext>'') {
			if (! $stmt->bind_param('s', $searchparam)) {
				$err='Query failure: BINDING PARAMETERS FAILED ' . $searchparam;
					header('Location: login_err.php?err=' . $err);
					exit();
			}
		}
		$result = $stmt->execute();
	}
	
	$fields = mysqli_fetch_fields($result);
	$headers = array();
	foreach ($fields as $field) {
		$headers[] = $field->name;
	}
	
	$fp = fopen('php://output', 'w');
	if ($fp && $result) {
		header('Content-Type: text/csv');
		header('Content-Disposition: attachment; filename="export.csv"');
		header('Pragma: no-cache');
		header('Expires: 0');
		fputcsv($fp, $headers);
		while ($row = $result->fetch_array(MYSQLI_NUM)) {
		fputcsv($fp, array_values($row));
	}
	die;
	}
}

function output_chem_listing_csv ($mysqli, $searchtext, $searchfor, $orderby, $showediting, $rootdirprefix, &$error_msg) {
	$prep_stmt = get_chem_listing_query($searchtext, $searchfor, $orderby, $searchparam, 0);
	echo $searchtext . ' : ' . $searchfor . ' : ' . $orderby . ' : ' . $searchparam . ' : ' . $prep_stmt . ' : '; //debug
	export_query_to_csv($mysqli, $prep_stmt, $searchtext, $searchparam);
}

This does export a CSV file, but the contents of the CSV file are (beginning with the output of the debug statement):
------------------
111 : c.ChemicalName : c.ChemicalName : %111% : SELECT c.`ChemicalID`,
c.`ChemicalName`,
c.`CommonName`,
c.`SDSDate`,
c.`Hazard`,
c.`ManufacturerID`,
m.ManufacturerName,
l.URL FROM `CONTENT_Chemicals` c
LEFT JOIN `CONTENT_Manufacturers` m ON c.ManufacturerID = m.ManufacturerID LEFT JOIN `CONTENT_ItemLinks` l ON c.`CurrPDFLinkID` = l.LinkID WHERE c.ChemicalName LIKE ? ORDER BY c.ChemicalName : <br />
<b>Warning</b>: mysqli_fetch_fields() expects parameter 1 to be mysqli_result, boolean given in <b>phpcnfg\include\listingfunctions.php</b> on line <b>21</b><br />
<br />
<b>Warning</b>: Invalid argument supplied for foreach() in <b>phpcnfg\include\listingfunctions.php</b> on line <b>23</b><br />

<br />
<b>Fatal error</b>: Call to a member function fetch_array() on boolean in <b>phpcnfg\include\listingfunctions.php</b> on line <b>34</b><br />

------------------
Line 23: foreach ($fields as $field) {
Line 34: while ($row = $result->fetch_array(MYSQLI_NUM)) {

------------------

As far as I can tell, what I need for the CSV export to work, is to either change "$result = $stmt->execute();" to "$result = $mysqli->query($prep_stmt);" and have it somehow accept parameters, or transform the result of "$stmt->execute();" to a mysqli_result object, which I can then use just like the result of $mysqli->query. Anybody know how to do that? There must be a way; I can't be the only one who needs CSV files of query results that have user-submitted parameters that need to be free of SQL Injection, but Google is somehow turning up nothing.

Thanks!


Katie

 

[vacunita]

You seem to be mixing procedural and object oriented mysqli calls. You can't. Pick a style and stick to it.

$stmt->execute(); does not return a result set, only true or false. So cannot be used with the procedural style mysqli_fetch_fields().

Since you are using the objected oriented execute(), then you need to keep using the object oriented methods to get the results of your query.

http://php.net/manual/en/mysqli-stmt.fetch.php

  $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($name, $code);

    /* fetch values */
    while ($stmt->fetch()) {
        printf ("%s (%s)\n", $name, $code);
    }

I would suggest you start by getting the results of your query back first to screen, before trying to save to a CSV. Once you have the results back and can see them, then you can worry about creating the CSV.


If you want to use $msyqli->query(), then you need to directly build your query and include your parameters in it directly as there is no binding.

The usual way to prevent injection there is to run the built query through $mysqli->real_escape_string().

$qry = "SELECT c.`ChemicalID`,
c.`ChemicalName`,
c.`CommonName`,
c.`SDSDate`,
c.`Hazard`,
c.`ManufacturerID`,
m.ManufacturerName,
l.URL FROM `CONTENT_Chemicals` c
LEFT JOIN `CONTENT_Manufacturers` m ON c.ManufacturerID = m.ManufacturerID LEFT JOIN `CONTENT_ItemLinks` l ON c.`CurrPDFLinkID` = l.LinkID WHERE c.ChemicalName LIKE [b][COLOR=#4E9A06]". $searchparam ."[/color][/b] ORDER BY c.ChemicalName"

$escaped_query = $mysqli->real_escape_string($qry);

$results = $mysqli->query($escaped_query);

http://php.net/manual/en/mysqli.real-escape-string.php

----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

 

[Katerine]

Thank you! With some modifications, this worked perfectly. Here was the finished code:

function export_query_to_csv($mysqli, $qry, $searchtext, $searchparam)
{
	$qry = str_replace("LIKE ?", "LIKE '" . $searchparam . "'", $qry);
	$qry = preg_replace('~[[:cntrl:]]~', '', $qry); // remove all control chars
	$prep_stmt = $mysqli->real_escape_string($qry);
	$prep_stmt = str_replace("\'%", "'%", $prep_stmt); //take care of the escaped LIKE clause
	$prep_stmt = str_replace("%\'", "%'", $prep_stmt);
	
//	echo $prep_stmt;
	$result = $mysqli->query($prep_stmt);
	
//	$fields = mysqli_fetch_fields($result);
	$fields = $result->fetch_fields();
	$headers = array();
	foreach ($fields as $field) {
		$headers[] = $field->name;
	}
	
	$fp = fopen('php://output', 'w');
	if ($fp && $result) {
		header('Content-Type: text/csv');
		header('Content-Disposition: attachment; filename="export.csv"');
		header('Pragma: no-cache');
		header('Expires: 0');
		fputcsv($fp, $headers);
		while ($row = $result->fetch_array(MYSQLI_NUM)) {
		fputcsv($fp, array_values($row));
	}
	die;
	}
}

function output_chem_listing_csv ($mysqli, $searchtext, $searchfor, $orderby, $showediting, $rootdirprefix, &$error_msg) {
	//[URL unfurl="true"]http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv[/URL]
	$prep_stmt = get_chem_listing_query($searchtext, $searchfor, $orderby, $searchparam, 0);
//	echo $searchtext . ' : ' . $searchfor . ' : ' . $orderby . ' : ' . $searchparam . ' : ' . $prep_stmt . ' : ';
	export_query_to_csv($mysqli, $prep_stmt, $searchtext, $searchparam);
}

Katie