How to secure a Query inside of a GET param?

Question

I need to secure a SQL Query inside an GET param from my website which directly accesses to my database.

The url would be like:

   xxx.de/entryAPI.php?query=`city`='Berlin'&...

which generates following SQL Query:

SELECT * FROM `xyz` WHERE `city`='Berlin' LIMIT 0, 20

The table contains much more columns and I need very different queries, so an prepared statement isn't possible for my.

I have an defined set of queries which should be possible, so would it be an idea to write the possible queries to an db and the get param has to be validated with this database, which contains all possible queries? Then an URL call only would generate an SQL Query if the query in the get param is also storaged in the database.

Thank you and I hope you understand my problem!

You should have a limited set of GET parameters `city`, etc. Then build the query based on the passed parameters. Bind the value. You also should URL encode your get parameters so values aren't lost. — chris85, Oct 29 '17 at 18:46
@chris85 This isn't possible because the table contains a huge amount of columns which all need to queried in different ways for different cases. — Daniel Janke, Oct 29 '17 at 18:49
You can generate a column safelist with `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = '[table_name]' AND COLUMN_NAME = '?'` and use an prepared statement — Raymond Nijland, Oct 29 '17 at 18:51
You cant just let a user pass whatever they want to your SQL. — chris85, Oct 29 '17 at 18:52
You should start by correctly formatting your query string, `?query=city=Berlin;` should be `?query=&city=Berlin;` Or such. — ArtisticPhoenix, Oct 29 '17 at 18:54
@chris85 Sure I know. So my idea would be an whitelist of queries. So if the user writes in an impossible query like ` xxx.de/entryAPI.php?query=`city`='Berlin'; DELTE * FROM Tablename`, which isn't on my whitelist my script wouldn't generate the Query for my db. Is that possible? — Daniel Janke, Oct 29 '17 at 18:57
@DanielJanke - you should never allow a user to pass in SQL, it wont work out well for you. — ArtisticPhoenix, Oct 29 '17 at 18:58
How are you going to know every city name? The column should be parameter name and the value should be the value being queried. Use a placeholder for the value, and the whitelist for the columns. — chris85, Oct 29 '17 at 18:58
@chris85 The amount of cities is 120, so I know all of them. Ok but sometimes I got much more complex queries like: `SELECT * FROM 'xyz' WHERE 'city'='Berlin' AND 'foo' > 120 AND 'bar' < 20 OR ...` How can I put something like this into a get Url? It would be too long. — Daniel Janke, Oct 29 '17 at 19:03
Maybe something like `query.php?city=Berlin&gt_foo=120&lt_bar=20` which is no where near to long, your actual current method would be longer. I think the limit for GET request is in the 8k range, so you aren't approaching it in either approach (https://stackoverflow.com/questions/2659952/maximum-length-of-http-get-request) — chris85, Oct 29 '17 at 19:09
Please mind that your api may require conditions like "not null", "like", ">", etc. and that will require large design changes. You may try more explicit filters: filter1_col=city&filter1_value=berlin&filter1_cond=like. Also this approach helps distinguish filters with other possible parameters. — Oleg Loginov, Oct 29 '17 at 19:27

ArtisticPhoenix · Answer 1 · 2017-10-29T21:06:19.307

You basically have 3 choices

Create separate methods that search on something specific. This would be set methods with pre-defined inputs.
Make it somewhat flexible but have separate methods. This would have set methods but with some flexabillity in the inputs.
Make it supper flexible so it can handle most queries.

This last one is non-trivial ( meaning hard ) coding wise, but once it's setup it could handle most everything you need. Not to mention it's "cool" so I will cover that in some detail.

Now what I would do is use JSON, because it's gonna give you way more flexibility structure wise. You can always use base64_encode() or some other encoding to sneak it into the $_GET array without issues with the url characters, or you can use $_POST for it. However you want to get it to the server is cool with me.

I would use a schema like this

query = {
    "groupOp":"AND",
    "expr":[],
    "group" : [{
           "groupOp":"OR",
           "expr":[{
                "field":"id",
                "op":"eq",
                "value":"2"
            },{
                "field":"id",
                "op":"eq",
                "value":"3"
            }],
            "group" : []
        },{
            "groupOp":"AND",
               "expr":[{
                    "field":"created_date",
                    "op":"gte",
                    "value":"2017-10-01"
                },{
                    "field":"created_date",
                    "op":"lte",
                    "value":"2017-10-31"
                 }],
               "group" : []
        }],
    }

Which would roughly work out ( provided a table is supplied )

SELECT
    *
FROM
    tbl
WHERE
    ( id = 2 OR id = 3 )
AND
    (created_date >= "2017-10-01" AND created_date <= "2017-10-31" )

Now just to point this out, the groupOp and group is very important for even simple queries like above. Compare the above query with this one.

SELECT
    *
FROM
    tbl
WHERE
    id = 2 OR id = 3
AND
    created_date >= "2017-10-01" AND created_date <= "2017-10-31"

-note- You can use other $_GET parameters for things like the sort order, sort index, table, limits etc.. ( like ?sort_index=id&sort_order=DESC&query=AJGEDC )

The groups denote sets of ( ), without them this second query has a whole different meaning. This is an Exclusive OR, because we need to compare the IDs as a set, then compare the date range. Without that we could match id=2 without the date range.

So now that I explained the theory you will have to write something ( probably a recursive function ) to compile the JSON into SQL

The basic rules would be.

group: a set of expressions ( expr )
groupOp: how expressions relate to one another within a group,and how nested groups relate to those expressions
expr: a set of query segments
field: the name of the column you want to compare
op: how the field is related to the data, for example: eq = equal, gte = greater then or equal, gt = greater then etc...
value: the value to compare against.

A few things to note for Security:

Never ever concatenate user supplied strings into your sql, without checking them first. If you do that you open yourself up to all kinds of Sql Injection nastyness. So take their column names from field and make sure they exist in the table the query is for before using them.
Use something like SHOW TABLES to get a list of tables from the DB, then compare user supplied tables with this list.
Use something like SHOW COLUMNS FROM table to get the table schema, then compare user supplied columns with this list.
Always use Prepared Statements. So build your query with placeholders, and an array of inputs. Then prepare and execute the query. such as SELECT ... WHERE id = :expr1.. and [':expr1' => 2].
If you allow sorting and limit, same thing applies don't concatenate, anything. Don't take shortcuts on security.

One other example with nested groups

query = {
"groupOp":"AND",
"expr":[],
"group" : [{
       "groupOp":"OR",
       "expr":[{
            "field":"id",
            "op":"eq",
            "value":"2"
        },{
            "field":"id",
            "op":"eq",
            "value":"3"
        }],
        "group" : [{
            "groupOp":"AND",
            "expr":[{
                "field":"id",
                "op":"in",
                "value":[5,6,7]
            },{
                "field":"cat",
                "op":"eq",
                "value":'product'
            }
        }]
    }],
}

With this being the SQL

SELECT 
    *
FROM
    table
WHERE
    (
        id=2
            OR
        id=3 
            OR
        (
            id IN( 5,6,7 ) 
                AND
            cat = 'product'
        )
    )

This query would find records with an id of 2,3 OR records with id Of 5,6,7 And a cat of "product".

You can also do them simple too:

{
    "expr":[{
            "field":"id",
            "op":"eq",
            "value":"2"
    }]
}

With SQL like this

SELECT 
    *
FROM
    table
WHERE
   id=2

So you can see by using JSON, we can have the complexity we would need for this, which would be hard to achieve using just URL query parameters. And trust me, something like this will need that added level of complexity at some time, so you might as well build it in to begin with.

Even with that added level of detail, you will still probably want multiple methods to deal with things like Joins and multiple tables. I don't know your exact use case, so maybe you only need it for one table. But, it can start to get real messy if you put to much detail in it. It's a balancing act.

I've built things like this before and they work pretty well, most the time it's pretty purpose built so I don't have anything that doesn't have a bunch of dependencies I can give you. And I don't have the time to write the entire thing up for you right now, as I have to change this light fixture ... long story.

Anyway, hope those Ideas help ( good Luck! )

How to secure a Query inside of a GET param?

1 Answers1