CODEWITHSUNDEEP
phpmysqlsecurity
Matt Fenwick
Matt Fenwick

Reputation: 49085

Using a hash to convert untrusted input to trusted input

A get request is issued to a php page. One of the key/value pairs is passed to this function, getReport, which accesses the mysql database and returns a json-serialized string:

function getReport($untrusted) {
    $tables = array(
        "day"        => "p_day", 
        "month"      => "p_month"
        ... keys are 'untrusted', values are 'trusted' table names .....
    );

    $trusted = $tables[$untrusted];
    if(!$trusted) {
        ... error out ...
    }

    $query = "select * from " . $trusted;

    .... access mysql database, do some other stuff ...
}

The question: is this secure? The untrusted input is only used as a lookup to get a trusted string. The trusted string is used to build a query.

Clarification:

the data is publicly available. I'm worried about SQL injection, or the user getting access to the connection parameters or to a table that isn't explicitly listed in $tables.

Upvotes: 3

Views: 220

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562270

I describe a similar method in my presentation SQL Injection Myths and Fallacies, and in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming. I called it Whitelist Maps but the idea is the same.

In my example, I use array_key_exists() because if you try to access a hash key that doesn't exist you'll get an error. Also if the key doesn't exist, in my example I choose a default instead of erroring out. But that's up to you, and either action may be the right thing depending on the application requirements.

You should also restrict the hash to just the list of tables you want to be accessed by a given web request. Don't just let any URL query any table in your database just because they get the name right. This means you need to create a different hash array for each instance where user input determines an SQL identifier (table or column name, etc.).

Upvotes: 1

Bryan
Bryan

Reputation: 2211

I will be bold enough to say yes, it's safe. You are controlling the possible values that are valid to use in the SQL query, so there is no way something unexpected gets injected into your query. Simple as that.

Obviously, if the data is protected, you will need access control on top of that.

Upvotes: 1

Dale Fraser
Dale Fraser

Reputation: 4758

I would say no. Can't anyone with the URL and hash browse the server if its GET it's just URL parameters. I guess the real answer requires more detail, what is being returned, etc.

Normally if I want a secure access, I would pass a username / password or token which is the user authentication.

Upvotes: 0

Related Questions