Display table name and field names in query url

Question

I was tasked with creating this Joomla component (yep, joomla, but its unrelated one), and the professor told me that he should make my code as dynamic as possible (code that requires less maintenance) and avoid hard coding. Initially, we assumed that url parameters are taken, turn them into objects, and pass them into a request.

Let's say we want to read the hotel with number 1 in the "hotels" table. let's say the table has the fields "hotel_id", "hotel_name" and some other fields.

Now, the approach we used to create the sql query string is to parse the url request, which looks like this:

index.php?task=view&table=hotels&hotel_id=1&param1=something&param2=somethingelse 

and turned it into a PHP object like this (shown in JSON equivalent, more understandable):

 obj = { 'table':'hotel', 'conditions':{ 'hotel_id':'1', 'param1':'something', 'param2':'somethingelse' } 

and the SQL query will be like this when the conditions are looped and added to the row where the field and value of the WHERE clause are the key and value of the object (still in JSON form for convenience):

 SELECT * FROM obj.table WHERE hotel_id=1 AND param1=something and so on... 

The problem that bothered me was exposing the table name and field names in the request URL. I know this poses a security risk by exposing elements that should only be seen on the server side. The current solution, which I think of, gives aliases for each table and field for the client side - but it will be hard coding, which contradicts its policy. and besides, if I did this and had a thousand tables for an alias, that would not be practical.

What is the correct way to do this without:

• hard code
• save code as dynamic and adaptable

EDIT:

As for arbitrary requests (I forgot to include this), what currently stops them at the back end is a function that takes a link from a hard-coded object (more like the configuration file shown here) and parses the url, choosing parameters or matching them.

The configuration looks like this:

 // 'hotels' here is the table name. instead of parsing the url for a table name // php will just find the table from this config. if no match, return error. // reduces risk of arbitrary tables. 'hotels' => array( // fields and their types, used to identify what filter to use 'structure' => array( 'hotel_id'=>'int', 'name'=>'string', 'description'=>'string', 'featured'=>'boolean', 'published'=>'boolean' ), //these are the list of 'tasks' and accepted parameters, based on the ones above //these are the actual parameter names which i said were the same as field names //the ones in 'values' are usually values for inserting and updating //the ones in 'conditions' are the ones used in the WHERE part of the query 'operations' =>array( 'add' => array( 'values' => array('name','description','featured'), 'conditions' => array() ), 'view' => array( 'values' => array(), 'conditions' => array('hotel_id') ), 'edit' => array( 'values' => array('name','description','featured'), 'conditions' => array('hotel_id') ), 'remove' => array( 'values' => array(), 'conditions' => array('hotel_id') ) ) ) 

and so, from this configuration list:

• If the parameters sent for the task are not completed, the server returns an error.
• if the parameter from url is doubled, only the first parameter read is taken.
• any other parameters not included in the configuration are discarded
• If this task is not resolved, it will not be specified for this table.
• If there is no task, the server returns an error
• If there is no table, the server returns an error

I actually patterned this by seeing a component in Joomla that uses this strategy. This reduces the model and controller to 4 dynamic functions that would be CRUD, leaving only the configuration file the only editable file later (this was what I had in mind about the dynamic code, I only add tables and tasks if additional tables are needed) but I am afraid that this may pose a security risk that I may not have known yet.

Any ideas for an alternative?

Answer 1

I have no problem using the same (or very similar) names in the URL and the database - of course, you can "expose" the implementation details, but if you choose radically different names in the URL and the database, you probably pick bad names. I am also a fan of consecutive naming - communicating with encoders / testers / clients becomes much more difficult if everyone calls everything something a little different.

What bothers me is that you allow the user to execute arbitrary queries in your database. http://.../index.php?table=users&user_id=1 , say? Or http://.../index.php?table=users&password=password (and not that you have to store passwords in clear text)? Or http://.../index.php?table=users&age=11 ?

If the user connected to the database has the same rights as the user sitting in front of the web browser, this may make sense. As a rule, this is not so, so you need a layer that knows what the user has and is not allowed to see, and this layer is much easier to write using the white list.

(If you stuck enough logic in stored procedures, then this might work, but then your stored procedures will contain hardcode column names ...)

Answer 2

When compiling an SQL query with data from input, it poses a security risk. But keep in mind that the values ​​of the columns are inserted into the fields, taking input from the user, analyzing him and composing an SQL query with him (with the exception of prepared statements). Therefore, when everything is done correctly, you have nothing to worry about - just limit users to these columns and tables. The open source program code / database is visible to everyone - and this will not harm the system, as you might think.

Answer 3

Your aliases can be rot13() in the meta / name of your objects.

Although, if you avoid typing accordingly when working with these names, I see no problem with the disclosure of their names.