Getting a Mysql Results without knowing a column name

Question

As I am still learning PHP and MySQL, I would like to know if it is possible to query a table without knowing it's name. Knowing the table I am querying and the column I would like to retrieve, I can write something like

$book_title=$row['book_title'];

Then I can use the resulting variable later in the script. In each case, each book category table will have a column of interest with a different name. I have several tables on which I am running queries. I am able to query any table by using a variable that always evaluates to the correct table name, because all the input from users corresponds to the tables in the database, so the $_POST super global will always carry a correct table name. The problem is for me to have a

$variable=$row['column'];

in cases where I do not know a column name before hand even though I know the table name.

My queries are simple, and look like

query="select * FROM $book_categories WHERE id=$id";
$row = mysqli_fetch_array ($result);
$variable=$row['?'];

The question mark say, I do not know what column to expect, as it's name could be anything from the tables in the database!

Since I have several tables, the query will zero on a table, but the column names in each table varies so I would like to be able to use one query that can give me an entry from such a column.

I hope my question is clear and that I am not asking for the impossible. If it's ambiguous, I care to elucidate (hope so).

Answer 1

I'm not sure what you mean, but it is possible to reference specifc columns by typing index (starting with 0) something like this: $row[0], $row[1] where 0 indicates the first column, and 1 indicates the second column from the returned recordset.

Example: If you have a select-statement like this:

SELECT title, author FROM books

You could reference these two columns with $row[0], $row[1]

If you try to get the value of $row[2] you will get an unassigned value because there are only two columns (0 and 1) from the recordset.

If you have a select-statement like this:

SELECT * FROM book_categories

and the recordset returns three columns, then you could access these with $row[0], $row[1] and $row[2]. $row[3] does not exist because there are only three columns (0,1 and 2)

Answer 2

Since you are learning maybe we could take some time to explain why this is possible but many people (including myself) would say this is bad -- or at least dangerous

Why you can

Your SQL query is basically a text string you send to the DB server, which decode that string trying to interpret it as SQL in order to execute the query.

Since all you send to the DB server is text string, you could build that string however you want. Such as using string interpolation as you did:

select * FROM $book_categories WHERE id=$id

That way, you could replace any part of your query by the content of a variable. You could even go further:

$query FROM $book_categories WHERE id=$id

Where $query could by SELECT * or DELETE.
And, why not initializing all those variables from a form:

$book_categories = $_POST['book_categories'];
$id = $_POST['id'];
$query = $_POST['query'];

Great, no? Well, no...

Why you shouldn't

The problem here is "could you trust those variables to only contain acceptable values?". That is, what would append if $book_categories somehow resolve to one table you didn't want to (say myTableContainigSecretData)? And what if $id resolve to some specially crafted value like 1; DELETE * FROM myImportantTable;?

In these conditions, your query:

select * FROM $book_categories WHERE id=$id

Will become as received by the DB server:

select * FROM myTableContainigSecretData WHERE id=1; DELETE * FROM myImportantTable;

Probably not what you want.

What I've tried to demonstrate here is called SQL injection. This is a very common bug in web application.

How to prevent that?

The best way to prevent SQL injection is to use prepared statement to replace some placeholders in your query by values properly shielded against SQL injection. There was an example posted a few minutes ago as a response to an other question: https://stackoverflow.com/a/18035404/2363712

The "problem" regarding your initial question is that will replace values not table or columns identifiers.

If you really want to replace table/columns identifiers (or other non-value part of your query) by variables contents, you will have to check yourself the content of each of these variables in order to prevent SQL injection. This is quite feasible. But that's some work...