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.

One of things that annoys me about SQL is that it can't think in terms of objects and it's lack of encapsulation makes me constantly have to escape commands to prevent injections.

I want a database language that can be polymorphic and secure. I have searched online for non-procedural database programming languages and so far my google search has been unsuccessful.

I know in languages like php there are ways to prevent the injections by making the PHP encapsulated well, but not all database programming situations involve embedding the database language in another language.

In situations where it's database programming only, is there a database programming language that is object oriented in itself? If not, are they working on one?

share|improve this question
    
Are you talking about SQL itself, or the API to connect and work with the database? SQL isn't procedural, so I'm a bit confused. –  Telastyn Dec 27 '12 at 18:04
    
Forget the procedural part. Just is there a database language with encapsulation? –  DrinkJavaCodeJava Dec 27 '12 at 18:07
add comment

1 Answer

SQL actually has encapsulation built into the language, specifically in the part you're talking about, for preventing injection. The basic idea is, if you're escaping your SQL queries as you build them, you're doing it wrong.

Wrong way:

SQL = 'select * from MY_TABLE where NAME = ' + Escape(nameParam);
RunQuery(SQL);

Query encapsulation in SQL is called using parameters. It looks something like this:

SQL = 'select * from MY_TABLE where NAME = :name';
Params.Add(nameParam);
RunQuery(SQL, Params);

This sends the query parameters to the database as something separate from the query itself, so it doesn't get parsed as part of the string, making injection impossible. The database engine substitutes the parameter in for the param token (:name).

This also has efficiency benefits. On the client side, you don't have to concatenate strings, and you can usually declare your SQL strings as constants. And on the database side, the DB engine can cache a parametrized query and use the same query plan if you reuse it multiple times, making data access faster.

Exactly how parameters work on the client side varies, based on the language database, and DB access library you're using. Look at your documentation to figure out how it's done. But AFAIK all SQL databases support it, so you shouldn't have much trouble being able to use them.

share|improve this answer
2  
@DrinkJavaCodeJava: SQL will also let you use execute stored procedures by passing in query parameters. Usually, the code for doing so is similar to the code for parameterizing inline SQL. If you're concerned about injection, one way to make it far more difficult is to change your permissions to only provide access to execute stored procedures, and then make sure those stored procedures are safe. –  Brian Dec 27 '12 at 18:32
3  
@Brian. Procs are themselves invoked by sql and are vulnerable to injection. Even when using procs, the parameterization described by MasonWheeler is needed. Here is an example of a proc execution sql with an injection hole: "myProc " + var1 –  mike30 Dec 27 '12 at 19:32
1  
I think you used the wrong word at the beginning there, you should replace that with ADO.NET / JDBC / Common database drivers. The escaping is necessary, but the drivers will do it for you; neither the SQL language nor any SQL servers will do this escaping for you so the way you explained this, it comes off a little inaccurate even though you understand the reality of the mechanism accurately. –  Jimmy Hoffa Dec 27 '12 at 20:07
2  
I'm referring to the opening statement SQL actually has encapsulation built into the language, specifically in the part you're talking about, for preventing injection. specifically that you say SQL has it in the language, when it is not in the SQL language or database, but rather the database connectivity librarys which you allude to at the end. –  Jimmy Hoffa Dec 27 '12 at 20:56
1  
I think that parameters support is a little different, the fact that a double quote is escaped when being handed across the pipe to the SQL server is still a necessity of the library considering if it is not, the server has no alternative but to assume that is the end of the parameter's value. The execution plan cache does cache parameterized queries but this meaning of "parameter" is more akin to "parameter" of a stored procedure which in no SQL requires a libraries "command" object to be executed, and when using query analyzer/management studio does require manual escaping of the parameters. –  Jimmy Hoffa Dec 27 '12 at 21:39
show 3 more comments

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.