×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Query with bind parameters, exported results to CSV

Query with bind parameters, exported results to CSV

Query with bind parameters, exported results to CSV

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

CODE --> php

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

RE: Query with bind parameters, exported results to CSV

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

CODE

$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().

CODE

$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 ". $searchparam ." 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...








----------------------------------
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

RE: Query with bind parameters, exported results to CSV

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

CODE --> php

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) {
	//http://stackoverflow.com/questions/125113/php-code-to-convert-a-mysql-query-to-csv
	$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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close