5

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?

gnat
  • 21,442
  • 29
  • 112
  • 288
DrinkJavaCodeJava
  • 215
  • 1
  • 1

1 Answers1

17

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.

Mason Wheeler
  • 82,151
  • 24
  • 234
  • 309
  • 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
  • @Mike: If your DB connection only has execute permission, "exec myProc + [EVIL Injection Code]" will trigger a permission error. Though obviously you should avoid that issue entirely by just using parameterized queries for the proc. Of course, a stored procedure can also execute dynamic sql explicitly (e.g., by calling `sp_executesql`). Don't do that. – Brian Dec 27 '12 at 19:58
  • 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
  • @Jimmy: What do you mean? As I said, the exact details of how it works varies from one DB access library to another, but the basic principle as I described it is accurate, and although I wrote it basically as pseudocode, the style is quite close to to the way I do queries in actual work on one of my projects. – Mason Wheeler Dec 27 '12 at 20:28
  • 3
    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
  • @JimmyHoffa: It's actually part of the database for at least some databases. I know MS SQL Server has Parameters support baked in, and I'm pretty sure Firebird does as well. They make it part of the database, and not "something the connectivity library interpolates into the query," so that they can cache the plan and not need to reparse it when you run the same query multiple times with different param sets. Those are the two I use most often. Not sure about other DBs. – Mason Wheeler Dec 27 '12 at 21:11
  • 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