andrewlocatelliwoodcock

Thoughts on Software

Archive for the ‘T-SQL’ Category

Using SQL and LEFT JOINs to find missing data

leave a comment »

It’s not unusual when working with data-driven applications to be asked to produce ad-hoc exception reoprst of the type: there should a at least one row in TableB for each row in TableA – can you tell me any rows in TableA that do not have at least one corresponding row in TableB?

How do you find something that’s not there?!

Our old friend the LEFT JOIN to the rescue. We already know from this post, that we can use LEF T JOINs to return every row from one table and any matching row from another and this is an extension of the same problem: in this case, we are looking specifically for every row in table A that does not have a matching row in table B.

Here’s how to do it:

SELECT
      a.Id
FROM
      TableA a
LEFT JOIN TableB b ON a.Id = b.Id
WHERE
      b.Id IS NULL

So, we’re returning everything in TableA, anything matching from TableB but then limiting the resultset to only those rows in TableA that DO NOT have a match in TableB. We do that with the statement:

WHERE b.Id IS NULL

This works because the database is returning all rows from TableA and matching rows from TableB but it still has to return something in the case where there are no matching rows in TableB: the special value NULL meaning “unknowable”. What our WHERE clause is saying is “only return those rows in TableA where the matching row in TableB is unknowable”, i.e. where we don’t have a matching row!

Simples.

Written by andrewlocatelliwoodcock

June 4, 2012 at 21:29

Posted in Databases, mySQL, SQL, SQL Server, T-SQL

Tagged with , ,

Introduction to SQL Injection

leave a comment »

I wrote a post recently about creating flexible WHERE clauses in stored procedures to support, amongst other things, requirements for flexible search functionality without resorting to building and executing dynamic SQL against the database. It realised afterwards that the concept of SQL injection is still not universally understood and that it would be a good idea to write a post explaining what it is, what threat it poses and how to guard against it. I will also reveal the worst, most SQL injection friendly piece of code I have ever come across …

SQL injection is a technique whereby an attacker manages to get your database to run SQL of their choosing thereby gaining access to your complete user list, causing the database server to drop a table or even a database and generally causing mayhem. Typically an attacker will append extra instructions or conditions onto a valid SQL statement and when that statement is executed so is the attacker’s extra code. An attacker gaining access to your user account table is bad enough but imagine if you’ve also been sloppy enough to have stored passwords, credit card details, etc. …

Read the rest of this entry »

Written by andrewlocatelliwoodcock

June 1, 2011 at 19:55

Posted in SQL Server, T-SQL

Tagged with , , ,

Flexible Search using Stored Procedures: varying the WHERE clause at run-time

with 4 comments

Stored Procedures offer a number of advantages when developing databases, perhaps the chief among them being performance and security. I am loathe to lose these advantages but because they are pre-compiled blocks of SQL code, it can be challenging to develop Stored Procedures that can meet requirements for very flexible functionality such as search.

Over the years, I have seen a number of attempts to provide flexible search functionality. The worst of these involved building SQL Script on the client machine and then passing that script to the server for execution … SQL injection, anyone? Just as bad were stored procedures where the entire where clause was built on the client before being executed on the server: simply passing the where clause as a parameter to the stored procedure does not prevent SQL Injection attacks. Another method is to create a series of stored procedures, one for each combination of search terms. This is used in conjunction with business logic which decides which search stored procedure to run; this approach prevents SQL injection attacks (unless the stored procedure is spectacularly badly written) and also means that each search term has its own execution plan computed. The main disadvantage to this approach is that the number of stored procedures required increases exponentially with each additional search term. In these situations, the technique I prefer sacrifices some performance in order to retain security and improve flexibility.

Read the rest of this entry »

Written by andrewlocatelliwoodcock

May 30, 2011 at 20:37

Posted in SQL Server, T-SQL

Tagged with , , ,

Inserting the results of a stored procedure into a table

leave a comment »

There’s an extremely useful feature in T-SQL that let’s you insert a rowset from a stored procedure or function directly into a table or (from SQL Server 2005 onwards) a table variable.

Where this comes in really handy is that it allows you to manipulate the output of a stored procedure outside the context of that stored procedure, basically allowing better code reuse.

This feature is not undocumented and is hardly a state secret but whilst I can always remember that it exists, I can’t always remember how to use it, hence this post …

Examples:

INSERT #myTemporaryTable EXEC myStoredProcedure

INSERT myTable EXEC myStoredProcedure

INSERT @myTableVariable EXEC myStoredProcedure

There is one caveat however: the structure of the table must match the output of the stored procedure, so if the stored procedure is updated, this can break unrelated code …

Written by andrewlocatelliwoodcock

May 6, 2011 at 14:44

Posted in SQL Server, T-SQL

Tagged with , , ,