About Quotation // SQL // PHP

Question

I've two 3 variable and that used in $sql string

$bikeid = xxxxx
$st_char = column name
$st_tab = table name

I've coded out like this

$sql = "select $st_char
        from $st_tab
        where bike_id like '$bike_id'";

And like this

$sql = "select ".$st_char."
        from dbo.".$st_tab."
        where bike_id like ".$bike_id;

To select data from my database,the result is the same,they can get data from database

My question is which one is right and which one is wrong if none wrong which one is better and why ?

Thanks

Don't use variable is Select clause: Example: `SELECT $columnName` FROM $tableName. Instead use the variables in WHERE's For Example: `SELECT columnName FROM tableName WHERE columnName = $variable`; — aldrin27, Aug 20 '15 at 01:33
They are both wrong, you should use parameterised queries instead. Currently you are vulnerable to SQL injection. — 1800 INFORMATION, Aug 20 '15 at 01:43
I've use variable because it **must** be used otherwise i must coded out all statement for each sql. Is it really to not to use variable in select clause how is it important and why. thanks — Trin Pongtaman, Aug 20 '15 at 01:45

score 0 · Accepted Answer · answered Aug 20 '15 at 01:44

Both are bad because they are vulnerable to SQL injection.

There are also potential performance gains from using prepared statements. So at the very least, your query should look like:

select $st_char from $st_tab where bike_id like :bike_id

Unfortunately, you can't use parameters in certain situations, like column and table names. In this case you will need to do manual string concatenation, but whitelist allowed input. For example:

$allowed_cols = array('col1', 'col2', 'col3');
$allowed_tables = array('t1', 't2', 't3');

if(in_array($st_char, $allowed_cols, true) && in_array($st_tab, $allowed_tables, true))
{
    $query = "select $st_char from $st_tab where bike_id like :bike_id";
    // perform execution here
}
else
{
    // invalid or malicious input
}

You may also want to wrap the table/column names in square brackets ([]) to avoid conflicts with any reserved keywords:

$query = "select [$st_char] from [dbo].[$st_tab] where bike_id like :bike_id";

since they are hard codded and not user input in the example this does apply — , Aug 20 '15 at 01:47
If they are not coming from an untrusted source, there's less of an impact. Still a good practice so you don't forget when it matters. There are also small speed boosts possible especially if you query the same thing with different parameters multiple times (eg. inserting many rows of data into a table). — Anonymous, Aug 20 '15 at 01:48
Not sure what you mean. MD5 is a hash. You can't decode it and has nothing to do with security. If you have an unrelated question, please start a new discussion. — Anonymous, Aug 20 '15 at 02:00

About Quotation // SQL // PHP

1 Answers1

Both are bad because they are vulnerable to SQL injection.