Home » SQL & PL/SQL » SQL & PL/SQL » Customized Query Builder Architecture (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Customized Query Builder Architecture [message #576481] Tue, 05 February 2013 08:49 Go to next message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

I got a task to send some alters based on the user query data (my requirements may sound vauge to you guys).

As user is not a SQL expert, so we have to provide him a query builder. Now to store user query, I decided to build a data model and keeping in mind the reusability of previously made queries (so that we can use them directly in further queries).

Like a user made two queries:
1. Find patient id, name, sugar_level, pulse_rate, weight for those patients whose diabitic level is greater than 'X'
2. Find patient id, pulse_rate for all those patients whose diabitic level is greater than 'X' and weight > 90

So first query can be directly used in second query. (Just a very simple example)

But I am pretty much confused with the decision of columns for each table to store query and how to relate them.

Tables I am able to identify is :

1. Table to store query level details, i.e. query_id, who created, query_short_name, query_description, operator used (SET, GROUP BY, HAVING, etc), distinct indicator, etc.
2. select clause table or columns selected table
3. From clause table
4. table to store group by clause
5. table to store having clause

I am trying to store seprate parts of the query into seprate tables, but not getting which columns to have in each table, how to relate them... and after that, for a given query_id, procedure to construct a whole query, and then validate/parse and execute it using dbms_sql and scheduler. I tried to create tables by taking few scenarios, but pretty much confused now.

Can someone help me with design, if he has gone thorough any such design.

Thanks,
Manu
Re: Customized Query Builder Architecture [message #576482 is a reply to message #576481] Tue, 05 February 2013 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>Can someone help me with design, if he has gone thorough any such design.

I can only hope that no one has ever deployed such an unprofessional & amateurish "design".

Just out of curiosity, how do you plan to avoid SQL Injection which can compromise data integrity or utterly destroy the database?
Re: Customized Query Builder Architecture [message #576483 is a reply to message #576482] Tue, 05 February 2013 09:11 Go to previous messageGo to next message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi BlackSwan,

Can you suggest me some alternate design for the same.

Also for SQL Injection, how someone will use this design to create sql injection, can you give me an example.

select <user_data> from <user_data> where <user_data> group by <user_data> having <user_data>

Can you fill something in user_data through a web applcaiton that can cause the SQL injection. User account executing the query will have very few priviledges (on the selected tables only).

Thanks for Suggestions.

Manu
Re: Customized Query Builder Architecture [message #576489 is a reply to message #576483] Tue, 05 February 2013 09:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Also for SQL Injection, how someone will use this design to create sql injection, can you give me an example


select username from all_users where 1=1 group by username having 1=1

Knowing all account names is one of the first step to hack a database.
There are many other queries like that to get so much information about the database.

Regards
Michel

[Updated on: Tue, 05 February 2013 09:35]

Report message to a moderator

Re: Customized Query Builder Architecture [message #576491 is a reply to message #576489] Tue, 05 February 2013 09:52 Go to previous messageGo to next message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Hmm, don't worry for that. He will not be able to store this type of queries.

As in the procedure, I will be checking for the table names in user_tables, columns in user_tab_cols against that table, etc. etc. before storing them. Definately, I can't go through all the possibilities of SQL injection, but give me scenarios, I think I will be able to handle.

But what is wrong in such type of design, only SQL injection, then user will not allowed to mess up the things, As its a Oracle Application for which I am trying to develope a plugin, already has very strict security. Any other architecture related suggestions.

Thanks,
Manu

[Updated on: Tue, 05 February 2013 09:53]

Report message to a moderator

Re: Customized Query Builder Architecture [message #576493 is a reply to message #576491] Tue, 05 February 2013 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
user will not allowed to mess up the things,


Don't be so confident, they WILL find a way, trust me.
There are many web pages talking about SQL injection, just search them.

And the design is not wrong only concerning security but also performances, local and global.

Regards
Michel
Re: Customized Query Builder Architecture [message #576494 is a reply to message #576493] Tue, 05 February 2013 10:26 Go to previous message
manubatham20
Messages: 472
Registered: September 2010
Location: Champaign, IL
Senior Member

Smile

Agree with you. I have good idea about this shit (SQL injection), good articles available on hackforums.

But let's not discuss this, I think I have to make this on my own. Performance will not be a concern, as its not going to cross even 10 million records till the system exist.

Thanks,
Manu
Previous Topic: Report with Date Range
Next Topic: create trigger to create view but give me error
Goto Forum:
  


Current Time: Sat Oct 25 14:03:02 CDT 2014

Total time taken to generate the page: 0.08150 seconds