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?
2
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.
8