This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 23, 2013 9:35 AM by jschellSomeoneStoleMyAlias RSS

Maintaining SQL Queries in a separate text file

1003591 Newbie
Currently Being Moderated
Hi,

I am working on web based project using Spring MVC framework and I am maintaining all SQL queries in a separate text file so that I can change them any time when needed. I just want to know am I doing right, it this a optmistict way of coding??
  • 1. Re: Maintaining SQL Queries in a separate text file
    PhHein Guru Moderator
    Currently Being Moderated
    Not good. The text file can be deleted, moved corrupted etc. I'd store the queries themselves in the database.
  • 2. Re: Maintaining SQL Queries in a separate text file
    1003591 Newbie
    Currently Being Moderated
    Thanks PhHein,

    But I just want to know one more thing. Storing queries in a database will connect database server twice to execute a single query ( first to fetch and second to execute ). Am I right??
  • 3. Re: Maintaining SQL Queries in a separate text file
    PhHein Guru Moderator
    Currently Being Moderated
    Yes, that's right.
  • 4. Re: Maintaining SQL Queries in a separate text file
    1003591 Newbie
    Currently Being Moderated
    Thanks PhHein !! :-)
  • 5. Re: Maintaining SQL Queries in a separate text file
    Joe Weinstein Expert
    Currently Being Moderated
    Well, you can do one query, to get all the SQL queries your app needs, then you can
    use any of them as needed thereafter. But then what other part of the application initializes
    the DBMS with the SQL you want to later retrieve? So the SQL will be somewhere in a file
    anyway. And what sort of DBMS? If it's anything businesslike, and if the application is too,
    I would store the SQL as stored procedures, so you get the performance benefit of the
    precompilation.
  • 6. Re: Maintaining SQL Queries in a separate text file
    rp0428 Guru
    Currently Being Moderated
    >
    I am working on web based project using Spring MVC framework and I am maintaining all SQL queries in a separate text file so that I can change them any time when needed. I just want to know am I doing right, it this a optmistict way of coding??
    >
    The best place to store the queries, and HOW to store them depends on your architecture and on how your application is being used.

    Is this a single-user application on a user's pc? Or is your application located on an application server in the middle-tier?

    The queries should be stored where they will be used. Your queries should be stored in the tier where your application code will reside. On the client PC for a single-user app, on the application server for a 3-tier app and incorporated into stored procedures/functions/packages if part of a database app.

    If your application is totally external to the database (e.g. a reporting app) then it doesn't make sense for you to become part of the database architecture. An org isn't going to let you create objects in the database to store your queries when your queries are not part of the database application. That puts the burden on them to backup your objects, restore them for your and provide support to you and your application.

    What format to store the queries in is a different issue. A major criteria is the type of queries you are using and how they are used. Many apps that query the database cannot just use hard-coded queries that are never modified. Users need to be able to provide parameters (e.g. name, ID, state, etc) to select just the data that they want.

    That means the base query itself may not even be valid syntax but needs to have a WHERE clause added. Prepared statements and queries that use bind variables ('WHERE ID = ?') are typically used for these.

    One method of storage is to use Java property files that use name=value pairs. Using property files makes it easy to use Java to find a query by name and load it for you. Then your code would have functionality to modify the query to provide the parameter 'bind' values and execute it.

    There are also frameworks available for handling database related functionality. There is no need for you to write your own libraries for this. I suggest you locate a suitable framework to use.
  • 7. Re: Maintaining SQL Queries in a separate text file
    rp0428 Guru
    Currently Being Moderated
    >
    But I just want to know one more thing. Storing queries in a database will connect database server twice to execute a single query ( first to fetch and second to execute ). Am I right??
    >
    No - that is not right.

    You only need to connect to the database ONE time. Sure - if you get the queries one at a time to execute then Java will use that ONE connection to make TWO roundtrips to the database: one to get the query to execute and then another one to submit the query for execution.

    And you don't need to get the queries one at a time only when you execute them. You could just get them all when your application starts up and store them locally. You could use a Map instance where the key is the name of the query and the value is the query itself.

    As I mentioned in my other reply unless your application is part of the database application and infrastructure itself you should not store your queries in the database. The DBAs are responsible for any and all database objects and code and it is not their job to create tables for you (or allow you to create them) and perform backup/restore and other admin services if your app is not part of the database infrastructure.

    The DBAs can provide a READONLY user to allow you to access the environments (PROD, TEST, DEV) and the tables/views that you need but you need to take care of you own queries.

    The exception to that would be if your project is part of the database. But for that use case your app should mostly be using database package functions and procedures that return REF CURSORs for you to use to access your data.
  • 8. Re: Maintaining SQL Queries in a separate text file
    1003591 Newbie
    Currently Being Moderated
    rp0428 Thanks for your reply.. Sorry I marked this post as closed so I didn't think that it is still visible to others, actually I am new to forums :-)

    My application code and query file both resides in application server. As I mentioned in other thread here is a sample code

    Query in text file :

    select label,control,controlid,valuetypescript,validationscript,style,cssclass,field_id from dynamic_controls where page_id=val0 and portfolio_id=val1 and location='val2'

    Call to QueryBuilder :

    String [] QueryVals={"10",session.getAttribute("portfolioid").toString(),"HO"};
    QueryBuilder(QueryFromFile,QueryVals);

    QueryBuilder defination :

    public String QueryBuilder(String Query,String[] Val)
    {
    for(int i=Val.length-1; i>=0;i--)
    {
    Val=FixSqlChars(Val[i]);
    Query=Query.replace("val"+i, Val[i]);
    }
    return Query;
    }

    Is the above code is prone to SQL injection? Will it perform worse? Should I really use prepared statement?

    You mentioned about some frameworks to use. Can you please highlight some? If I am not wrong you are talking about iBatis / Hibernate wright??

    Can you please put some sample code / links here?
  • 9. Re: Maintaining SQL Queries in a separate text file
    Tolls Journeyer
    Currently Being Moderated
    1000588 wrote:
    Is the above code is prone to SQL injection? Will it perform worse? Should I really use prepared statement?
    I would say "yes" to using a PreparedStatement, but not for SQL injection reasons, unless someone external to your system is able to access this method using their own parameters. I would recommend it because otherwise Oracle is going to have to do a hard parse of the query everytime it is run. If this is a rare query then not so much of a problem, if not then you are putting unecessary load on your database, and possibly (again depending on how much this query is used) causing problems with your SQL pool.
  • 10. Re: Maintaining SQL Queries in a separate text file
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    1000588 wrote:
    Is the above code is prone to SQL injection? Will it perform worse? Should I really use prepared statement?
    I would say "yes" to using a PreparedStatement, but not for SQL injection reasons, unless someone external to your system is able to access this method using their own parameters.
    That is not really the cause of sql injection, more common it is a risk when you cram values that you take from for example a web form into it - when a user of the system has direct influence over the value itself.

    So if the query is isolated you wouldn't need PreparedStatement for injection protection, but I'm a big supporter of "just doing it anyway" if only to make it a habit to write safe(r) code.
  • 11. Re: Maintaining SQL Queries in a separate text file
    Tolls Journeyer
    Currently Being Moderated
    gimbal2 wrote:
    Tolls wrote:
    1000588 wrote:
    Is the above code is prone to SQL injection? Will it perform worse? Should I really use prepared statement?
    I would say "yes" to using a PreparedStatement, but not for SQL injection reasons, unless someone external to your system is able to access this method using their own parameters.
    That is not really the cause of sql injection, more common it is a risk when you cram values that you take from for example a web form into it - when a user of the system has direct influence over the value itself.

    So if the query is isolated you wouldn't need PreparedStatement for injection protection, but I'm a big supporter of "just doing it anyway" if only to make it a habit to write safe(r) code.
    That's what I said, isn't it?
    If the method (which simply replaces the placeholders val0, val1 etc with text) takes it's values from the user (ie 'someone external to yoru system') then it is susceptible, but if not then it isn't.
  • 12. Re: Maintaining SQL Queries in a separate text file
    gimbal2 Guru
    Currently Being Moderated
    Tolls wrote:
    That's what I said, isn't it?
    I quote:
    unless someone external to your system is able to access this method using their own parameters
    I think I misread the meaning behind that. Moving along.
  • 13. Re: Maintaining SQL Queries in a separate text file
    939520 Explorer
    Currently Being Moderated
    You mentioned weather you should consider iBatis/Hibernate. Those are advanced topics. I suggest you stick with JDBC until you master all aspects of that first.
    As previously mentioned, I suggest you always use preparedStatements. Also, as a side note, I suggest you develop a habit of refactoring all your code over and over until its clean, compact, and easy to read.
    I value easy to read code above minor performance gains which may make it harder to read.
  • 14. Re: Maintaining SQL Queries in a separate text file
    1003591 Newbie
    Currently Being Moderated
    Thanks for reply gimbal2,

    The QueryVals array get created after checking each and every value for nulls and Chars that may harm SQL query. I am not using prepared statement, if I do so, I need to pass one extra argument which is ValTypeArray to identify the data type of value so that I can choose between functions like preparedStatement.setInt(..) or preparedStatement.setString(..) etc.

    For example :

    String [] QueryVals={"10",session.getAttribute("portfolioid").toString(),"HO"};

    String [] QueryValType={"int","string","string"};
    or
    int [] QueryValType={0,1,1};

    PrepareStatement(QueryVals,QueryValType);
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points