SQL query dosnt know variables

Question

I am trying to query some tables in my database using a simple dropdown in which the name of the tables are listed. the query has only one record result showing the name and age of the youngest institute registered in the database!

$table = $_GET['table'];
$query = "select max('$table'.est_year) as 'establish_year' from '$table' ";

I need to send the name of the table as variable to the querier php file. no matter the method is GET or POST in both ways when I put the variable name in the query statement, it gives the error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.order) as 'last' from 'customers'' "

Are you sure the error message points at that line exactly? There's nothing like what the error message says in that query. — JJJ, Jun 23 '12 at 11:25
Are you sure your GET doesn't have any extra trailing spaces? — Anirudh Ramanathan, Jun 23 '12 at 11:27
When I type the name of the tables manually, everything is ok and well. And I am sure there is nothing as space following the get value — , Jun 23 '12 at 11:30

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

You are wrapping the table name in single quotes, which is not valid SQL (that's the syntax for strings, not table names). You should either not wrap the name at all or else wrap it in backticks (on the american keyboard layout, that's the key above TAB).

You should also not quote the alias established_year:

select max(`$table`.est_year) as establish_year from `$table`

Also, your code is vulnerable to SQL injection. Fix this immediately!

Update (sql injection defense):

In this case the most appropriate action would likely be to validate the table name against a whitelist:

if (!in_array($table, array('allowed_table_1', '...'))) {
    die("Invalid table name");
}

edited May 23 '17 at 10:34

Community

1
1

answered Jun 23 '12 at 11:24

Jon

428,835
81
738
806

do I need to use mysql_escape_string() or what ? – Jun 23 '12 at 11:39
@rezasaberi: No for two reasons: 1) `mysql_escape_string` should never be used (`mysql_real_escape_string` has superseded it) and 2) it's a table name, where the rules are different. See the updated answer. – Jon Jun 23 '12 at 11:44
@Jon : How OP code is vulnerable to SQL Injection? And how mine is not preventing and yours is preventing? – Fahim Parkar Jun 23 '12 at 11:51
@FahimParkar: Please see the comment under your own answer. Using a whitelist prevents injections because only a handful of known safe inputs will be accepted. – Jon Jun 23 '12 at 11:54

score -1 · Answer 2 · edited May 23 '17 at 12:26

-1

single quote ('), in mysql, it represents string value.

SELECT *, 'table' FROM `table`;

Demo

So your query should be

$table = $_GET['table'];
$query = "select max($table.est_year) as 'establish_year' from $table ";

Also read old post, phpmyadmin sql apostrophe not working.

Also your code is vulnerable to SQL Injection. You can use something like this

//Function to sanitize values received from the form. Prevents SQL injection

function clean($str) {
    $str = @trim($str);
    if(get_magic_quotes_gpc()) {
        $str = stripslashes($str);
    }
    return mysql_real_escape_string($str);
}

$firstName = clean($_POST['firstName']);
$lastName  = clean($_POST['lastName']);
.
.
.

edited May 23 '17 at 12:26

Community

1
1

answered Jun 23 '12 at 11:31

Fahim Parkar

30,974
45
160
276

1

-1: This code will not prevent SQL injection because `$table` will be a *table name*, not just any string. The quoting rules are different. – Jon Jun 23 '12 at 11:45
@Jon : Do you see `return mysql_real_escape_string($str);`?? – Fahim Parkar Jun 23 '12 at 11:49
I do. You have to realize that `mres` achieves nothing if the returned value is not enclosed in quotes, which in this case it cannot be because it is not a string. – Jon Jun 23 '12 at 11:53

SQL query dosnt know variables

2 Answers2

Update (sql injection defense):

Demo