Retrieve result set metadata from the database without knowing the SQL statement

Question

I am using PHP and HTML and created a database. I'm connected to the database. I'm wondering if there is a way to retrieve data from a database by asking the user to enter the entire SQL statement. I'm really interested in how to get the data without saying $row['id']

For the sake of argument, they will know the columns and IDs and everything about the database.

Everything I've seen is that you have to know which columns are being queried before you can access the data.

Answer 1

If your users are to provide SQL statements to be used, they must know the names of the tables and columns they want.

You can query the information_schema.COLUMNS table to find table and column names and data types.

Alternatively, after executing the query, you can obtain the column metadata. For PDO, use $stmt->getColumMeta($columnNumber);. Using mysqli use ->result_metadata() like this (not debugged).

$stmt = $mysqli->prepare("SELECT what,ever,else FROM table");
$stmt->execute();
$metadata = $stmt->result_metadata();
$column_count = $mysqli->field_count();
$column_names = [];
for ($colnum = 0; $colnum < $column_count; $colnum++) {
    $field = $metadata->fetch_field();
    $column_names [] = $field->name;
}
$metadata->close();

This uses ->fetch_field() to get a lot of useful information about each column in the result set.

But asking users to provide you with SQL statements is a big security risk. If you trust users to provide you with SQL statements, you must also trust them not to corrupt or corrupt your data. If your system is open to the global internet, you will lose data. Sometimes people ask me how I know this.