1

I am building a reporting tool where an user can enter a SQL query and the tool will return the results in a CSV file. In addition to just writing to a CSV file, I also need to perform some additional logic here. So SELECT INTO OUTFILE will not work for me.

I know that executing arbitrary user provided SQL queries is bad, but this tool is going to be used only internally, so security shouldn't be a concern. Also I am limiting it to only select queries.

Now when I export the data in CSV format, I also want to output the column names of the query as the first row in the CSV file.

So my question is, is there a way to fetch the column names of a SQL query in PHP using PDO?

Mysql client tools like Sequel Pro are able to display the column names while displaying query results. So I am assuming that it should be possible, but I am not able to find it.

Sudar
  • 18,954
  • 30
  • 85
  • 131
  • 1
    Possible duplicate of [Include headers when using SELECT INTO OUTFILE?](https://stackoverflow.com/questions/5941809/include-headers-when-using-select-into-outfile) – P.Salmon Dec 09 '18 at 12:28
  • Keys of row array are the names of columns. – u_mulder Dec 09 '18 at 12:29
  • of course its possible , consider your sql query results a multidimensional array , so before you display rows and columns , just get first row array's keys. foreach($result[0] as $key=>$val) echo $key; – Suleyman DOGAN Dec 09 '18 at 12:32

2 Answers2

1

The keys of the row result are the column names. You can display them like so:

$conn = mysqli_connect(YOUR_CONNECTION_INFO);
$result = mysqli_query($conn, YOUR_QUERY);
$row = mysqli_fetch_assoc($result);

foreach ($row as $key=>$value) {
    echo $key;
}

You said security wasn't an issue since the code would only be used internally, but what happens if you create a new database user with limited rights and connect to the database using that user.

That way you can set up the rights as you want from your database and won't have to worry about users dropping tables.

Bobray
  • 87
  • 1
  • 2
  • 11
  • I am already limiting only `select` queries. Creating a new database user with limited rights will be an additional check. Will explore that as well. – Sudar Dec 10 '18 at 04:26
1

Here I am not writing full PDO connection code. You can use below code/logic to get the return column name.

$stmt = $conn->query("SELECT COUNT(*), first_column, second_column FROM table_name");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$columns = array_keys($row);

print_r($columns); // array(0 => COUNT(*), 1 => first_column, 2 => second_column)
Rohit Rasela
  • 425
  • 3
  • 6