"DA SQL" – Safe Client-Side SQL Querying

SQL and Multi-Tier

Traditionally, SQL has been constrained to be used in two-tier client/server applications, or on the server (a.k.a. "middle") tier of multi-tier apps.

In the classic client/server scenario, client applications would contain or generate SQL statements that were run directly against the database, without any means for business logic or fine data access restrictions to be applied in between. Because clients had full access to the SQL of the back-end server, they could query any data they pleased, and make extensive changes without control. Great flexibility on the client-side was achieved by sacrificing control.

When Multi-tier architecture came to replace client/server applications, direct SQL access to the back-end server was banished to the middle tier. Only the business server could communicate directly with the back-end database through SQL, while clients were usually restricted to retrieving full record sets, or sets of data filtered by criteria specifically exposed through the business server (maybe the server would expose specific method where clients could ask for data filtered by a given field). This consolidated control over data access and updates to the middle tier – where it belongs – but sacrificed flexibility on the client in how data could be queried and obtained.

(Some so-called multi-tier solutions try to circumvent this problem by simply passing SQL through from the client to the database; that is a Very Bad Idea™, as it completely bypasses all business logic in the middle tier, and essentially turns the middle-tier server into a glorified proxy. The result is really a Client/Server application, once again.)

Enter DA SQL

DA SQL, a technology introduced by and unique to Data Abstract, changes all that, by providing clients the full flexibility of SQL queries to describe their data needs without giving up the control held in the middle tier. While traditional client/server applications would allow clients to write SQL that ran directly against the back-end database, as discussed above, DA SQL statements sent from the client application are processed and run against the data as published by the business tier, allowing that tier to keep full control over data access and updates.

DA SQL statements will use field and table names as they are defined in the middle-tier schemas of the Data Abstract server, and will automatically be restricted to the data and fields that the middle tier allows access to.

For example, an extensive Customers table might be exposed to a client application for sales personnel with two restrictions: (a) only a subset of, say, 10 fields is available and (b) every sales person may only access customers from his or her region. This is data access business logic that would be encoded in the schema or in custom code written for the server application, making sure that whenever a client application retrieves or updates data, these restrictions are upheld.

DA SQL allows client applications to use SQL to perform data queries, without giving up or bypassing this (or any other) business logic in the process. For example, the client application might send the following query to the middle-tier server:

Whether the application allowed the end-user to type this query or generated this SQL code as part of a query builder or some other form of UI, the intention is the same: the application is asking for all fields of the Customer table where the customer in question is named Miller. But as we recall, data access constraints were set on the server, so the client will not actually receive all fields of the customer table (nor all customers, world-wide, that are named Miller): because DA SQL is processed and executed in the middle tier, business logic will be applied to constrain the access and enforce the business rules we set forth above. As a result, the actual database query that will run against the server might look something like this:

DA SQL, which has deep understanding of the back-end database /and/ subset that the schema exposes to clients, was able to craft a new query – combining the details from the request it received from the client with its own filters, in both SELECT and WHERE clause.

DA SQL Flexibility

While the above is a simple example (and, to boot, a data request that could have easily been expressed, client side, without the need for SQL), DA SQL allows much more complicated and detailed requests to be formulated, which would still benefit from full protection of business logic.

For example, a query could JOIN together different tables, employ nested queries or have a much more elaborate and complicated WHERE clause. Still, DA SQL would enforce that data can be accessed as permitted by the schema and other server-side logic.

At the moment, DA SQL supports a subset of the SQL92 standard, with most querying options commonly used, including JOINs, nested queries and of course extensive SELECT and WHERE clauses to describe subsets of fields or records to retrieve. Over time, it will be expanded to support all features of SQL92 that are applicable. More details on the current set of supported SQL features can be found here in our wiki.

How Does it Work?

It is important to understand that DA SQL query statements are not, ever, directly passed through to the back-end database, thus completely eliminating the risk of SQL injection attacks or malicious clients executing unwanted actions as part of what looks like an innocent SELECT request.

When a query is received by the DA SQL engine on the middle-tier server, it is parsed and processed into an internal structure describing the SELECT, INSERT, UPDATE or DELETE statement it represents, much like would be done on a regular back-end database engine. This structure is then compared to the tables and fields defined in the schema and validated against it – for example, a statement touching a table (or fields of a table) not exposed in the schema would be rejected with an “Unknown table” error – even though the actual table might exist in the back-end database.

Once the query is validated, DA will go ahead and build its own SQL statement (or set of statements), in the syntax of the respective back-end databases (“DB SQL”) to fetch the data as needed. Here, it will apply any and all business constraints to make sure it accesses only data that is permitted (for example, as seen above, a query with * asking for all fields would be translated to only retrieve the fields actually published via the schema). It will also apply any column mappings or database abstraction defined in the schema.

Depending on the complexity of the original DA SQL query and the type of the data sources on the back end needed to fulfill it, DA might be able to generate a single “DB SQL” statement, or it might have to run separate queries (for example to fulfill a JOIN spanning separate back-end databases) and combine data internally before sending it back to the client.

All of this is happening transparently on the server – all the client sees is the resulting data matching its query.

Find Out More

DA SQL is mainly a server-side technology, and currently available in DA/.NET Servers and the cross-platform standalone Relativity Server. It can of course be consumed from clients written in all three editions of the Data Abstract library, including .NET, Xcode and Delphi.

For .NET and Silverlight client development, an interesting technology built on top of DA SQL is DA LINQ.

You can read more about DA SQL in our wiki, or check out the DA SQL samples shipping with the products.