Query from PHP code

PHP
1

Good day,

We are developing a commercial website using PHP and MySQL. We have different kind of customers, and we have created tables for each kind.

We have also developed an administrative area for reviewing the purchases and customers information.
We select the kind of customers we want to check in the first page of the administrative area.

Here some examples of tables and variables involved:

  • In the first page the kind of customer is selected from a combo box, and passed to the next page as a post variable
  • Customers tables have names like:
    Table Customers_Kind1
    id
    Customer_Name
    …

So, depending which customer is selected in the first page, the query will involve a different table, for instance:
$SQL = “SELECT Customer_Name FROM Customers_Kind1”
$SQL = “SELECT Customer_Name FROM Customers_Kind2”

In order to have just one generic query to the database, could you please let me know the correct syntaxis to concatenate strings using PHP, and use the post variable with the selected customer to create the SQL query?
Something like this:
$SQL = “SELECT Customer_Name FROM Customers_”.$POST[‘selected_customer’]

Thanks a lot!!

2

First off, it is $_POST[‘selected_customer’], second, that is a SQL Injection waiting to happen. Be sure to use mysql_real_escape_string on it or use a prepared statement or mysqli functions.

example:

$SQL = "SELECT Customer_Name FROM Customers_".mysql_real_escape_string($_POST['selected_customer']);

Lastly, just testing your code would have indicated if you did it right or not and would have identified you were referencing your POST data incorrectly.

3

Proper design should be a single table with a column indicating Cust. Type


$SQL = "SELECT CUSTOMER_NAME FROM CUSTOMERS WHERE CUSTOMER_TYPE = '" [COLOR=#000000][COLOR=#0000BB]. mysql_real_escape_string[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]$_POST[/COLOR][COLOR=#007700][[/COLOR][COLOR=#DD0000]'selected_customer'[/COLOR][COLOR=#007700]])[/COLOR][/COLOR] . "'"
4

Hi cpradio,

Thanks a lot for your answer.
Could you please tell me more about function mysql_real_escape_string?
Why not just add the $_POST variable?

Also, in case I am using a $SESSION variable, as I have to use it in MySQL queries in several pages, is it the same format?
I mean:
$SQL = "SELECT Customer_Name FROM Customers".mysql_real_escape_string($_SESSION[‘selected_customer’]);

Thanks a lot!!!

5

By using $_POST[‘selected_customer’] directly, you are setting yourself up for a SQL Injection (google it for more information).

By using mysql_real_escape_string() you are preventing a SQL Injection from happening.

As for, do you need to use it with your $_SESSION variable? Maybe, it depends on how you stored the $_POST data into session.

If you did (I recommend this method)

$_SESSION['selected_customer'] = $_POST['selected_customer'];

Then you definitely need to use mysql_real_escape_string()

If you did (I actually do not recommend this method, as you really should store the posted data as is, so displaying it back to the user won’t be affected)

$_SESSION['selected_customer'] = mysql_real_escape_string($_POST['selected_customer']);

Then you do not necessarily need to use mysql_real_escape_string again.

6

Hi cpradio,

Thanks a lot for your answer.
Reading about SQL Injection I understood about the function mysql_real_escape_string(). I’m clear now I have to use it.

This is what I am thinking to do:
On page 1, I have a dropbox for selecting the customer.
Depending the selection, I know which custimer I have to use, so which suffix I have to add to the SQL query.
I put this suffix in a session variable, as I will need it in several pages to use it to build SQL queries.
In each page, I can create a local variable for being use in this page, for instance:
$_SESSION[‘selected_customer’] = $customer;

And the use it for the SQL query:
$SQL = “SELECT Customer_Name FROM Customers_”.mysql_real_escape_string($customer);

Is it ok?

Thanks a lot!!!

7

Yes, except you have the local variable assignment backwards.

$customer = $_SESSION['selected_customer'];
8

I’d just like to point out that using mysql_real_escape_string() will not prevent SQL injection attacks because there are no single quotes encasing its value. This means that we can still manipulate the query to whatever we’d like:


$evil = ' WHERE id = 1';

$query = mysql_query('SELECT * FROM table'.mysql_real_escape_string($evil));

It’s just as well MySQL’s PHP interface does not support stacked queries (or as far as I know, at least), otherwise the attacker could wreak havoc with your tables:


$evil = '; DROP TABLE table--';

$query = mysql_query('SELECT * FROM table'.mysql_real_escape_string($evil));

I’d personally do what K.wolf stated above, have another column stating the type of customer they are. However if you do want to do it this way, then validate the HTTP POST data with a pre-set list of known table names:


$valid = array('A1', 'B2', 'C3');
if(!in_array($_POST['key'], $valid, TRUE))
{
	die;
}
9

Use the following if you are going to go with a table per customer setup to prevent the SQL Injection

$SQL = "SELECT Customer_Name FROM `Customers_".mysql_real_escape_string($_POST['selected_customer']).'`';
10

Grave accents aren’t escaped by MRES, only quotes (single & double) and backslashes; so it’s still vulnerable to SQL injection:


$evil = '` WHERE id = 1#';

$query = mysql_query('SELECT * FROM `table'.mysql_real_escape_string($evil).'`'); #selects where column ID is equal to 1
11

Okay, tie it to proper validation of the $_POST data and he should be good. @Sir_Arcturua ;, be sure to verify the selected_customer is valid and contains only valid characters (a-z0-9) before using it.