I have a script where you select the users you want and then we export that information to CSV, I dont know how many names the post array will receive so I am adding them all together into a new array and then doing a select where in (that array) to find them and add them to the csv output.

The following works fine when you have just one person to export, but the minute you add more the csv only has the headers in. I have run checks on the select statement and I believe this is where it is going wrong as when I have two or more names the row count is 0. I have printed the new ($match_array1) and it has the values in there so maybe its the sql statement that is not correct, it is not throwing any errors or warnings:

if(isset($_POST['header'])){
          $user_in_post = $_POST['user_result'];

$match_array1 = array();
foreach($user_in_post as $user_to_check)
{
    $match_array1[] = $name_to_check;
    
}
$hdr_user_to_export = implode(",",$match_array1);
        
    $sql = "SELECT first_name, last_name FROM names WHERE first_name IN ('".$hdr_user_to_export."')"; 
    $results = $DB_con3->query($sql); 

  
    $output = "";
`
//Set header values
$headers = array("First Name", "Last Name");
`
//Insert header values to $output
foreach($headers as $h){
    $output .= $h . ",";
}
$output = rtrim($output, ","). "\n";

//Iterate through results
while($row = $results->fetch(PDO::FETCH_ASSOC)) {
    foreach($row as $cell){
        //Comma-separate each value
        $output .= $cell.",";
    }
    //Remove last comma of each line and add newline
    $output = rtrim($output, ",") . "\n";
}

//Set headers
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="ORA_ASE_FUNCTIONAL_SECURITY_CUSTOM_ROLES.csv"');

//Output
echo $output;

exit;
 
  }   `

Thanks!

    "SELECT first_name, last_name FROM names WHERE first_name IN ('".$hdr_user_to_export."')"

    As well as the SQL injection vulnerability caused by directly inserting user-supplied data into a SQL statement (prepared statements exist for a reason), then (assuming the user is not being malicious or adding names that contain apostrophes) you'll only be searching for one string that consists of all the names at once. And you probably don't have any records in your table that have that.

      In addition to using a prepared query (you can use FIND_IN_SET(), rather than IN(), so that you only need one place-holder), your code should -

      1. Validate the input data before using it. If no user(s) have been selected, there's no point in running the rest of the code.
      2. If you are selecting from existing users, you should submit the user's id (auto-increment primary index), not the user's first name. In real-life, you can end up with several users with the same first name.
      3. If the query doesn't match any data, you should output a message to the user, rather than to proceed to output an empty csv.
      4. Most of the loops, concatenation, trimming, and variables in this code are not needed. Just implode the headers and each fetched row of data to produce the desired output.

      pbismad (you can use FIND_IN_SET(), rather than IN(), so that you only need one place-holder),

      ...assuming MySQL.

          Thanks for these replies, I will look at your points. I plan on putting in validation, but one question: the data in the post array is from the database, the user has no form to edit the data. What are the risks of not sanitizing?

          Never heard of FIND_IN_SET before, just looking into it, and mysql tutorial states that it can only work over two arguments, in this case I dont have two arguments. I just have one which is find a match in the array, I see examples of FIND_IN_SET(Fred, '$hdr_user_to_export') but what I have tried is FIND_IN_SET('$hdr_user_to_export') and this throws a syntax error at this point in the statement. I am using pdo over mysql, what am I missing?

          Thanks again!

          I think you're missing the first_name argument to the function. ("FIND_IN_SET"? Find what in set?)

            DesignerSeries the data in the post array is from the database, the user has no form to edit the data

            I'm not sure what that means. How are the 'users' for the report being selected?

            In any case, all external data that gets submitted to a web site can be set to anything by a user/bot and cannot be trusted. You must protect against sql special characters in data values from breaking the sql query syntax, which is how sql injection is accomplished.

            DesignerSeries I dont have two arguments

            Yes you do. You have the column holding the data and the list of value(s) (hopefully supplied via a prepared query place-holder) you want to match against that column.

            You should end up with code that looks like the following -

            <?php

$errors = []; // array to hold error messages

if(isset($_POST['header']))
{
	// validate the input data
	if(empty($_POST['user_result']))
	{
		$errors['users'] = 'You must select one or more users.';
	}
	
// perform any other validation here...

// if no errors, use the submitted data
if(empty($errors))
{
	$sql = "SELECT first_name, last_name FROM names WHERE FIND_IN_SET(id,?)"; // note: per the forum reply, you should be matching specific users via their id, not via a first name match which would give you all users with the same first name(s)
	$stmt = $DB_con3->prepare($sql); // not sure why you are up to '3' for a connection variable name. Your application should have one database connection.
	$stmt->execute([implode(',',$_POST['user_result'])]);
	$rows = $stmt->fetchAll(); // you should set the default fetch mode to PDO::FETCH_ASSOC when you make the connection, so that you don't have to specify it in every fetch statement

	// if no matching data, set up an error message
	if(empty($rows))
	{
		$errors['users'] = 'No user data was found.'; // since you are (probably) selecting from existing users, this error would indicate a programming mistake or someone is submitting nonexistent ids.
	}
	
	// if no errors, use the result from the query
	if(empty($errors))
	{
		//Set header values
		$headers = array("First Name", "Last Name");

		//Insert header values to $output
		$output = implode(',',$headers) . "\n";

		//Iterate through results
		foreach($rows as $row)
		{
			$output .= implode(',',$row) . "\n"; // note: the original code was missing the concatenation dot . when adding to $output
		}

		//Set headers
		header('Content-Type: text/csv');
		header('Content-Disposition: attachment; filename="ORA_ASE_FUNCTIONAL_SECURITY_CUSTOM_ROLES.csv"');

		//Output
		echo $output;
		exit;
		
	} else {
		// output any errors
		echo 'The following error(s) occurred:<br>';
		echo implode('<br>',$errors);
	}
}
}
                  Write a Reply...