Protecting SQL Server Data from Accidental Deletion

In nearly every project that I worked on in recent years, the database not only stores the data maintained by the application, but also describes (parts of) the application itself. If you ever had to implement a permission system which grants users to view or edit tables or open forms or execute functions, you already know that.

The resulting problem is that if the application relies on certain key data to be present and correct, accidental modification or deletion of that data usually causes the application to fail.

I try to show how to use triggers to prevent accidental data modification.

Prevent table data deletion

The simplest way to prevent data deletion is to have an INSTEAD OF DELETE trigger which does nothing, or simply raises an error:

CREATE TRIGGER [dbo].[Prevent_Foo_Delete]
    ON [dbo].[Foo]
    INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    RAISERROR('You cannot delete Foo',1,0);
END

If you really need to delete data, use the DISABLE TRIGGER (2005, 2008) and ENABLE TRIGGER (2005, 2008) commands.

Conditional deletion prevention

In this case, deletion should only be allowed under certain conditions. For example, we could allow to only delete single records:

CREATE TRIGGER [dbo].[Prevent_Foo_Multi_Delete]
   ON  [dbo].[Foo]
   INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF (SELECT COUNT(ID) FROM deleted) > 1
        RAISERROR('You can only delete a single Foo',1,0);
    ELSE
        DELETE Foo
        FROM Foo INNER JOIN deleted ON Foo.ID = deleted.ID

Similarly, one could prevent the deletion of detail records to only a single master record by writing

    IF (SELECT COUNT(DISTINCT BAR_ID) FROM deleted) > 1

Preventing Modifications

The same method can be used for UPDATE triggers. It may be, however, easier to define an ON UPDATE trigger to avoid rephrase the UPDATE statement in an INSTEAD OF trigger. In case of failure, we rollback the current transaction:

CREATE TRIGGER [dbo].[Prevent_Foo_Update]
   ON  [dbo].[Foo]
   FOR UPDATE
AS
BEGIN
      SET NOCOUNT ON;

      IF (SELECT COUNT(ID) FROM inserted) > 1 BEGIN
            ROLLBACK TRANSACTION
            RAISERROR('You can only modify 1 Foo',1,0);
      END
END

Preventing Truncation

These mechanisms prevent you from an accidental UPDATE or DELETE on all records (e.g. by a missing WHERE clause, or semicolon in front of the WHERE condition).

However, there is still the TRUNCATE TABLE command which deletes all data in a table and cannot be stopped by a DELETE trigger:

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

The rescue shows in the preceding sentence:

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint

Simply have a table that references the tables to be protected:

CREATE TABLE Prevent_Truncate(
    Foo_ID INT REFERENCES Foo(ID),
    Bar_ID INT REFERENCES Bar(ID)
)

You only appreciate how valuable your data is once it’s lost 😉

Introducing oraddlscript

A recent question on StackOverflow inspired me to write a utility called oraddlscript:

How to generate scripts for Oracle schema objects, such as tables, procedures, etc.

The answers directed me to the DBMS_METADATA package (9i documentation, 10g documentation). The function DBMS_METADATA.GET_DDL is the functional equivalent of the MSSQL SMO library, which prompted me to adapt my command-line utility SMOscript to Oracle databases. Voilà, oraddlscript.

oraddlscript 0.14.3584.16268 (c) by devio.at 2009

    list and script databases and database objects.

    usage: oraddlscript [options] [command]

    options: (leading '-' or '/')

    -s server       TNS name or host name or host:port
    -svc service    service name (if host name is provided)
    -o owner        owner name

    -u username     username (default: integrated authentication)
    -p password     password (if -u is missing, password for sa)

    -f filename     output to file
    -F directory    output to directory

    -A              current ANSI codepage
    -O              ASCII
    -T              Unicode
    -U              UTF8

    commands:

    l               list objects
    s               script object/s (using dbms_meta.get*ddl)
    -xml            use dbms_meta.get*xml

    list object owners on server (implied by -s)
    list objects in database (implied by -s -o)
    script all objects (implied by -s -o -F/-f)

The command-line arguments are consistent with SMOscript, except for -d (database) which has been replaced by -o (owner name).

The list of objects is retrieved by querying DBA_OBJECTS, ALL_OBJECTS and USER_OBJECTS depending on which of the catalog views is accessible by the user specified by -u.

The package also contains a function GET_XML which is used to retrieve the XML representation of a database object.

The functions of oraddlscript are:

  • list usernames of object owners
  • list objects of specific owner
  • generate CREATE scripts of objects owned by specific user
  • generate XML files representing objects owned by specific user
  • generate one file per object or single file for all objects

Of course, logging and batch operations work just as previously described for SMOscript.

oraddlscript is available for download here.

Generating Table of Contents from WordPress Export

WordPress implements an Export function which allows bloggers to download the contents of their blog as a single XML file.

In a previous post I described an XSLT file to convert the exported XML into a single HTML file. A click on an article’s title displayed the whole article using a little JavaScript and CSS.

This time, I wanted to create an HTML table of contents displaying all the blog’s categories. Upon selection of a category, only the posting titles of that category should be displayed. The titles link to the original blog articles.

The new XSLT is available for download here.

Applied to this blog, the generated Table of Contents is here.

dbscript New Version 0.99

The latest version 0.99 of dbscript has been released today providing new functionality and a couple of fixes.

Data diagrams looked a bit distorted if the data model contained circular foreign key constraints. I sketched the problem in my article on cycle detection, and the data diagram now excludes circular foreign keys in the calculation of the tables’ positions.

Comparison results can be restricted to “scopes”, such as new objects only, dropped objects only, etc. This makes it easier to generate schema migration scripts without dropping objects, for example.

Documentation Generators provide a preview to the generated content, and the generated XML now contains the project and project version identifiers to enable linking and referencing in the generator’s output.

Scripting a table in the object’s Generate/Create page now includes all constraints and indexes. (The project version script always included child objects). The same applies to object comparisons of tables, so that changes to indexes etc are easily identifiable.

New Functions

Besides generating .png data diagrams, dbscript now has the capability to generate data diagrams for Dia, an open-source diagrammer. The layout routine is the same as for png’s, but the output is Dia’s native XML format. Generating for Dia means that developers can freely layout and edit the diagram according to their needs, and export it to other formats. I described this feature earlier, and included samples.

Schema comparison is one basic feature of dbscript, and the new version compares multiple versions in one operation. After defining which schema versions to compare, you get a comparison matrix showing the number of differences between any two versions.

If the selected versions are versions of the same schema at different points of time, the comparison timeline shows each object ever changing in any of the versions, along with an indicator of the change.

Within a project, you can define Branches (as known from version control systems) and assign project versions to a branch. This alone would not be too overwhelming, but branches are a precondition of the update notification system, which I will describe in a future post.

The latest version of dbscript is available for download here.

Please leave comments and feedback.

Get Absolute URL of ASP.Net Application

In one of my web projects, I needed to pass the name of a specific URL inside the application to another application. So the first task was: how do I find the absolute URL of an ASP.Net application’s root directory.

I tried ResolveClientUrl, which, according to MSDN, returns

A fully qualified URL to the specified resource suitable for use on the browser

As I was expecting an absolute URL from this description, it turned out, it doesn’t. (There also seems to be a terminological confusion between a Fully Qualified Domain Name, and Absolute and Relative URLs)

Both ResolveUrl and ResolveClientUrl create URLs relative to the page’s URL or the application root, but no absolute URL.

Fortunately, I found this entry on geekpedia, which provided a solution (that I was very close to develop on my own 😉 )

public string FullyQualifiedApplicationPath
{
  get
  {
    //Return variable declaration
    string appPath = null;

    //Getting the current context of HTTP request
    HttpContext context = HttpContext.Current;

    //Checking the current context content
    if (context != null)
    {
      //Formatting the fully qualified website url/name
      appPath = string.Format("{0}://{1}{2}{3}",
        context.Request.Url.Scheme,
        context.Request.Url.Host,
        context.Request.Url.Port == 80
          ? string.Empty : ":" + context.Request.Url.Port,
        context.Request.ApplicationPath);
    }
    if (!appPath.EndsWith("/"))
      appPath += "/";
    return appPath;
  }
}

Of course, if you are inside a Page’s context, this reduced version is sufficient

string appPath = string.Format("{0}://{1}{2}{3}",
  context.Request.Url.Scheme,
  context.Request.Url.Host,
  context.Request.Url.Port == 80
    ? string.Empty : ":" + context.Request.Url.Port,
  context.Request.ApplicationPath);
if (!appPath.EndsWith("/"))
  appPath += "/";

Bad (MS SQL Server) habits to kick

Aaron Bertrand of SQLblog.com is writing a great series called Bad habits to kick, and two of his articles cover issues similar to some of my more recent posts:

Choosing the wrong data type deals with all SQL Server data types, where I only compared NVARCHAR vs VARCHAR. (Although I have to admit, I guess I won’t kick my habit of declaring INT integers even if the values never exceed 1-byte or 2-byte values 😉 )

His article on Inconsistent naming conventions reads like a confirmation of some articles of my series on Data Model Checks, where I described how to check naming conventions for tables and other objects, columns, and constraints and indexes.

Remember that consistency in naming and typing improves efficiency in programming. Once you decided for some kind of conventions, your need to look up column names and data types in the table definitions will reduce as it will become “obvious” for the programmers which column name uses which data type, and how a table or stored procedure is named exactly.

Scripting all MS SQL Databases using SMOscript

SMOscript user Eric reported that the tool fails to script all remaining objects of a database once an error occurs trying to generate a CREATE PROCEDURE statement for an encrypted stored procedure.

This problem has been fixed in the latest version 0.14 which is now available for download.

Based on his feedback I want to demonstrate more capabilities of SMOscript:

Logging

While SMOscript does not implement log file functionality, you can still redirect its output to files from the command line using the standard shell redirectors >, >> and 2>.

smoscript ...parameters... >filename.log 2>filename.err

Scripting all databases on a server

To script all databases of a server into separate files in distinct directories, you can use SMOscript to first list all databases, then invoke SMOscript to script each database in the original list:

set basepath=c:\path\to\output\
set first=

for /f in "usebackq" %%i in (`smoscript -s localhost`) do ↵
                                         (set db=%%i& call :smo)
goto :end

:smo

if "%first%"=="." (
    echo database %db%
    mkdir %basepath%%db%
    smoscript -s localhost -d %db% -f %basepath%%db%\create.sql ↵
                                         > %basepath%%db%.log
)
set first=.

goto :eof
:end

The first line of “smoscript -s” is not a database name, so we include it from the list by using the variable named “first”.

The simple combination of SMOscript with a bit of shell magic can be quite efficient.

SQL Server Wishlist?

Through this post on Stack Overflow I found an article called My SQL Server Wishlist which has a list of useful enhancements to SQL Server, along with links to their Microsoft Connect entries.

You can also see this article as a list of shortcomings or pitfalls for unexperienced MSSQL / TSQL developers.

Some of these entries, of course, have never been answered or commented on by MS, which makes me wonder.

Parsing Culture-Invariant Floating-Point Numbers

I just came across this non-obvious behavior of double.TryParse:

We develop on German Windows, but the input field for float values should be intelligent to recognize both German (“,”) and international (“.”) notations for the decimal point.

Of course the functions to call to parse a string s into a double value are:

double f; string s = "double value as string";

double.TryParse(s, System.Globalization.NumberStyles.Float,
    System.Globalization.CultureInfo.InvariantCulture, out f)

double.TryParse(s, out f)

But the results were surprising

InvariantCulture default culture
string value return (bool) output (double) return (bool) output (double)
“100,03” false true 100.03
“100.03” true 100.03 true 10003.0

My reasoning was, that if current (UI) culture could not parse the string, I let Invariant Culture try to parse it.

It turns out, however, that if the current culture simply removes all characters it does not recognize as valid input string for float numbers, and then parses the string, with the undesired result as seen above.

So the right way seems to try with invariant culture first, then use current culture:

double f;
double? Result;
if (double.TryParse(s, System.Globalization.NumberStyles.Float,
        System.Globalization.CultureInfo.InvariantCulture, out f))
    Result = f;
else if (double.TryParse(s, out f))
    Result = f;
else
    Result = null;

Code Generation with PowerShell and TFS

If you use PowerShell to automatically generate code for your project (e.g. during the build process) and you work in TFS-based code, you need to check out existing files before overwriting them. Otherwise the files are read-only and/or not stored in TFS after code generation.

The PowerShell stub script to handle this situation looks like this (assuming the .ps1 file is also inside a TFS directory):

$scriptdir = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
$basepath = $scriptdir.Substring(0, $scriptdir.Length - "path\from\tfs-base\to\script".Length)

$scriptdir stores the directory name of the currently executed script. If the script file is stored inside your TFS project, you can calculate the file path to checkout from $scriptdir.

Next, we call TFS checkout, generate code, and check in again:

& .\tf-checkout.cmd $basepath
... Code generation is here ...
& .\tf-checkin.cmd $basepath

tf-checkout.cmd needs to set the Visual Studio environment variables (as in Visual Studio Command Prompt) to execute the “tf checkout” command:

@echo off
setlocal
call "c:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" x86

echo.
echo checking out...

tf checkout %1path\to\file1.cs
tf checkout %1path\to\file2.cs
...

endlocal

tf-checkin.cmd looks similar:

@echo off
setlocal
call "c:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" x86

echo.
echo checking in...

tf checkin /comment:autogenerated /noprompt %1path\to\file1.cs
tf checkin /comment:autogenerated /noprompt %1path\to\file2.cs

endlocal