Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Where SQL statements are executed from a program, what is the best practice to store the statement text?

Most often it is in a read only string, in the source file it is executed in.

I think a better approach would be to have a separate text file containing all SQL statements, that are then read in to a dictionary object where the key is the purpose of that statement. Statements can then be executed by using their key, decoupling database implementation from the program and allowing reuse of statements between program objects.

Is this efficient? Is there a better way? Thank you!

share|improve this question
1  
That approach is debatable. Reading from a text file is more costly then from a table. –  Maxood Nov 27 '12 at 16:32
    
If you don't want the code in the program put it in stored procedures. There is enormous debate in industry over whether stored procedures or in-code are better, I land strongly on the stored-procedures side myself (as well as do most engineers these days that I've run into) but that is again still something that has been heavily debated, and the large use of ORMs these days to some makes it moot and to others is used as an argument against stored procedures. You'll have to sift through a great deal of opinion and tales of whoa on both sides to find which side you're on. –  Jimmy Hoffa Nov 27 '12 at 16:35
1  
Well, stored procedures are pretty much going to preclude any sort of dynamic SQL building, @JimmyHoffa. My gripe with stored procedures is that the development environment for them (at least in SQL Server) is just terrible. The performance and security benefits are undisputable, but the differences are smaller than most SP advocates suggest. –  Robert Harvey Nov 27 '12 at 16:45
1  
@MichaelT: A data access layer still executes SQL; you've merely pushed the complexity somewhere else, though I will grant you that executing external SQL statements must be justified by a specific need. –  Robert Harvey Nov 27 '12 at 16:48
2  
@RobertHarvey its pushed into a library that is unit tested. It can be reused (the OP's reason for having them on a file system). Be it raw sql, stored procedure invocations, or an ORM, I believe it should be extracted from the application to promote consistent reuse. Runtime loading of the statements gives me the shivers. –  MichaelT Nov 27 '12 at 17:13
show 5 more comments

2 Answers 2

The only reason I can think of to treat SQL as data instead of code is if you are dynamically generating SQL statements, and you don't need to store those.

There are actually advantages to going in the other direction; by hard-coding your SQL statements into Stored Procedures, you benefit from certain security and performance optimizations that the RDBMS can provide.

SQL is already naturally "parameterized;" if you need additional flexibility beyond that, you should carefully balance your application requirements against the additional security challenges that will accrue from treating your SQL statements as data. SQL injection is one example of a potential security leak that can occur.

If you need generalized CRUD (create, read, update, delete) statements for each of your tables, you should use an Object-Relational Mapper to generate them.

share|improve this answer
    
at least mention the possibility of using sprocs to make your answer comprehensive :P –  Jimmy Hoffa Nov 27 '12 at 16:57
    
@JimmyHoffa: Done. :) –  Robert Harvey Nov 27 '12 at 17:01
    
SQL injection can occur even if you use you use stored procs. Injection prevention is about parameterizing your arguments. It has nothing to do with sql as data or code. I can give examples of injection through stored procs called in a non-parameterized way but it won't look good in a comment. –  mike30 Nov 27 '12 at 21:04
    
@mike: Creating SQL statements dynamically using string concatenation is how most people inadvertently produce SQL injection vulnerabilities, and it's easy to get that wrong. Having such vulnerabilities in sprocs is much less common. –  Robert Harvey Nov 27 '12 at 21:07
    
It's just as easy to inject with stored procs as it is with inline or dynamic sql. Consider this: cmd.CommandText="exec Myproc " + userInput; That's a glaring injection hole. Of course it's ridiculous to have un-parametrized arguments to your proc, but you could say the same thing when dealing with dynamic or inline sql. –  mike30 Nov 27 '12 at 21:17
add comment

I'd say the best way is to use Stored Procedures, if the DBMS supports it, for the following reasons:

  • They centralize the logics so you don't need to keep multiple copies in sync, which seems to be what you cares most.
  • They centralize the permission control (authorization) to the DBMS.
  • They are pre-compiled so they run faster.
  • (minor) The string size sent over the network is smaller (code and DBMS usually run on different servers).
share|improve this answer
    
+1 for mention of network traffic, that had never occurred to me –  Daniel Nov 28 '12 at 8:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.