SQLAjax Tutorial Lesson 2 - Security and Email Registration

Tutorial #2: SQLAjax Security, Part 1

In Tutorial #1, you learned how easy it is to pass an XML string to a SQL stored procedure using SQLAjax. Now you will start learning how to parse and utilize the XML tree from within the SQL stored procedure. This tutorial assumes that you are using SQL Server 2005 or later, but you can also write your own versions of the SQL Server xml functions with SQL Server 2000, MySQL, Oracle, or DB2. Future versions of SQLAjax will include MySQL, Oracle, and DB2-compatible object creation scripts.

The stored procedure below is one of several security gateways provided by SQLAjax. When it comes to security, every situation is different. Some developers prefer to handle security using existing C# or Java code written by themselves or others, but experienced SQL developers usually choose to implement security with tables and stored procedures. Through the SQLAjax custom HTTP handler for .NET or Java, you can implement as much additional security as you want using Java or .NET. However, SQLAjax provides all of the security you need, including C2 level security.

    ALTER procedure [dbo].[xsqlajax]
	    @xmlin xml = null
    as
    -- some web servers require the following in order to use xml methods
    SET ARITHABORT ON

    -- get the message type and call the appropriate stored
    -- procedure. the name of the message type and the name
    -- of the stored procedure can be different to support
    -- obfuscation
    -- 1st log: log xml in and json out for troubleshooting
    insert xSQLAjax_messagelog values (getdate(), @xmlin, NULL, NULL)

    -- verify XML coming in
    if @xmlin is null
    -- SAMPLE OF INCOMING XML MESSAGE, USED WHEN NO XML PARAMETER PASSED IN
    SELECT @xmlin = '< Message>< MessageType>xemailsubmit< /MessageType>
		< EmailAddress>someone@somedomain.com< /EmailAddress>< /Message>'

    -- use xpath and the SQL 2005 .value, .query, and .nodes functions
    -- to parse or "shred" XML trees
    declare @messagetype varchar(50)
    select @messagetype = @xmlin.value('(/Message/MessageType)[1]', 'varchar(50)') 

    if @messagetype = 'xreflection' execute xreflection @xmlin
    if @messagetype = 'xemailsubmit' 
    begin
	    declare @emailaddress varchar(100)
	    select @emailaddress = @xmlin.value('(/Message/EmailAddress)[1]', 'varchar(50)')
	    insert xSiteUsers (susEmailAddress, susCreated, susLastUpdated)
	    values (
				@emailaddress
			    ,	getdate()
			    ,	getdate()
			    )
	    select 'Download instructions sent to ' + @emailaddress + '. Welcome to the SQLAjax Family!'
    end

As you can see in the xsqlajax stored procedure above, the "message type" XML tag can define the stored procedure to be called, but does not normally use the same name as the stored procedure. The xsqlajax security gateway procedure provides one of several places where you can obfuscate your code and hide secure information (such as stored procedure names). Our next stored procedure will do more than the xreflection stored procedure did. It will actually create a record in the database for a new web site user (you), and it will then send an email message with a link you can use to download the SQLAjax code library.

To download your free SQLAjax code files now, provide your email address and click the button below. An email message will be sent to you immediately with a link to the SQLAjax files you need:

You should now receive an email that will provide you with a link to the download page and all of the remaining tutorials.