Rob Kraft's Software Development Blog

Software Development Insights

Archive for the ‘SQL Server’ Category

Improving ASP.Net Framework Membership Performance

Posted by robkraft on April 9, 2023

I’ll admit this post would have been more valuable fifteen years ago, but those still using the ASP.Net Membership provider tables and stored procs that came with .Net Framework 2.0, 3.0, or 4.8 my like the performance boost you can get from these changes. The Membership tables were designed with two major flaws, in hindsight. One, the primary keys are guids, which provide terrible performance, and two, they assumed most installations would have multiple applications using the same membership table, which I think they do not. But Microsoft made the application ID, a guid, part of every key in every query. If your application only has a single application ID, you can change the stored procs to not consider the application ID as part of the query, and change the indexes to remove application ID as well. The change scripts are provided below:


alter PROCEDURE [dbo].[aspnet_Membership_GetUserByEmail]
    @ApplicationName  nvarchar(256),
    @Email            nvarchar(256)
AS
BEGIN
    IF( @Email IS NULL )
        SELECT  u.UserName
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  
               
                u.UserId = m.UserId AND
                m.LoweredEmail IS NULL
    ELSE
        SELECT  u.UserName
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  
                
                u.UserId = m.UserId AND
                @Email = m.LoweredEmail

    IF (@@rowcount = 0)
        RETURN(1)
    RETURN(0)
END
GO

alter PROCEDURE dbo.aspnet_Membership_FindUsersByEmail  
    @ApplicationName       nvarchar(256),  
    @EmailToMatch          nvarchar(256),  
    @PageIndex             int,  
    @PageSize              int  
AS  
BEGIN  
 
    -- Set the page bounds  
    DECLARE @PageLowerBound int  
    DECLARE @PageUpperBound int  
    DECLARE @TotalRecords   int  
    SET @PageLowerBound = @PageSize * @PageIndex  
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound  
  
    -- Create a temp table TO store the select results  
    CREATE TABLE #PageIndexForUsers  
    (  
        IndexId int IDENTITY (0, 1) NOT NULL,  
        UserId uniqueidentifier  
    )  
  
    -- Insert into our temp table  
    IF( @EmailToMatch IS NULL )  
        INSERT INTO #PageIndexForUsers (UserId)  
            SELECT u.UserId  
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m  
            WHERE  m.UserId = u.UserId AND m.Email IS NULL  
            ORDER BY m.LoweredEmail  
    ELSE  
        INSERT INTO #PageIndexForUsers (UserId)  
            SELECT u.UserId  
            FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m  
            WHERE  m.UserId = u.UserId AND m.LoweredEmail LIKE @EmailToMatch 
            ORDER BY m.LoweredEmail  
  
    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,  
            m.CreateDate,  
            m.LastLoginDate,  
            u.LastActivityDate,  
            m.LastPasswordChangedDate,  
            u.UserId, m.IsLockedOut,  
            m.LastLockoutDate  
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p  
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND  
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound  
    ORDER BY m.LoweredEmail  
  
    SELECT  @TotalRecords = COUNT(*)  
    FROM    #PageIndexForUsers  
    RETURN @TotalRecords  
END  
go

ALTER PROCEDURE [dbo].[aspnet_Membership_FindUsersByName]
    @ApplicationName       nvarchar(256),
    @UserNameToMatch       nvarchar(256),
    @PageIndex             int,
    @PageSize              int
AS
BEGIN

    -- Set the page bounds
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @TotalRecords   int
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId uniqueidentifier
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT u.UserId
        FROM   dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE m.UserId = u.UserId AND u.LoweredUserName LIKE @UserNameToMatch
        ORDER BY u.UserName


    SELECT  u.UserName, m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
            m.CreateDate,
            m.LastLoginDate,
            u.LastActivityDate,
            m.LastPasswordChangedDate,
            u.UserId, m.IsLockedOut,
            m.LastLockoutDate
    FROM   dbo.aspnet_Membership m, dbo.aspnet_Users u, #PageIndexForUsers p
    WHERE  u.UserId = p.UserId AND u.UserId = m.UserId AND
           p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  @TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    RETURN @TotalRecords
END
GO




ALTER PROCEDURE [dbo].[aspnet_Membership_GetUserByName]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime,
    @UpdateLastActivity   bit = 0
AS
BEGIN
    DECLARE @UserId uniqueidentifier

    IF (@UpdateLastActivity = 1)
    BEGIN
        -- select user ID from aspnet_users table
        SELECT TOP 1 @UserId = u.UserId
        FROM     dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   
                @UserName = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1

        UPDATE   dbo.aspnet_Users
        SET      LastActivityDate = @CurrentTimeUtc
        WHERE    @UserId = UserId

        SELECT m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut, m.LastLockoutDate
        FROM    dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE  @UserId = u.UserId AND u.UserId = m.UserId 
    END
    ELSE
    BEGIN
        SELECT TOP 1 m.Email, m.PasswordQuestion, m.Comment, m.IsApproved,
                m.CreateDate, m.LastLoginDate, u.LastActivityDate, m.LastPasswordChangedDate,
                u.UserId, m.IsLockedOut,m.LastLockoutDate
        FROM     dbo.aspnet_Users u, dbo.aspnet_Membership m
        WHERE   
                @UserName = u.LoweredUserName AND u.UserId = m.UserId

        IF (@@ROWCOUNT = 0) -- Username not found
            RETURN -1
    END

    RETURN 0
END
GO


alter PROCEDURE [dbo].[aspnet_Roles_RoleExists]
    @ApplicationName  nvarchar(256),
    @RoleName         nvarchar(256)
AS
BEGIN
    IF (EXISTS (SELECT RoleName FROM dbo.aspnet_Roles WHERE @RoleName = LoweredRoleName  ))
        RETURN(1)
    ELSE
        RETURN(0)
END
GO



drop index aspnet_Membership_index on [dbo].[aspnet_Membership]
go
CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]
(
	[LoweredEmail] ASC
)WITH (FillFactor=95) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ApplicationId] ON [dbo].[aspnet_Membership]
(
	[ApplicationId] ASC
)WITH (FillFactor=95)

GO

drop index aspnet_Users_index on [dbo].[aspnet_Users]
go
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users]
(
	[LoweredUserName] ASC
)WITH (FillFactor=95)

GO
drop index aspnet_Users_index2 on [dbo].[aspnet_Users]
go
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users]
(
	[LastActivityDate] ASC
)WITH (FillFactor=95)

CREATE NONCLUSTERED INDEX [IX_AspNet_Users_ApplicationID] ON [dbo].[aspnet_Users]
(
	[ApplicationId] ASC
)WITH (FillFactor=95)

GO

Advertisement

Posted in Coding, SQL Server | Tagged: | Leave a Comment »

How to enable Rich Text for Text Boxes in Access Linked to SQL Server

Posted by robkraft on February 12, 2022

Text Boxes on Forms and Reports in Microsoft Access allow you to configure the field to display “Rich Text” instead of “Plain Text” by setting the “Text Format” property of the control. This allows you to use HTML formatting in the field and display portions of the field using bold, italics, underline, and other enhancements.

However, when your data is coming from a SQL Server (or other DBMS), through linked tables, you can only choose to configure the control to support “Rich Text” when the field has a size of 256 bytes or greater.

Therefore, you need to go back to your source database (SQL Server), increase the size of the field so that it is 256 or larger, then go back to Access and Relink the table. After that, you should be able to choose Rich Text for the field on the form.

When the linked field length is too short, you will get this message from Microsoft Access “The setting you entered isn’t valid for this property.” when you try to change the control.

Posted in Access, SQL Server | Leave a Comment »

Resolving SQL Server DatabaseMail Error:  Cannot send mails to mail server. (Failure sending mail.)

Posted by robkraft on February 7, 2022

I learned some information I thought could help others more quickly resolve this error on SQL Server. As security demands increase, some features of older versions of products get more difficult to maintain. In this case, I am supporting a SQL Server running on SQL Server 2014. It has the latest Cumulative Update applied (CU) yet the emails sent via DatabaseMail are still failing. They were failing occasionally but began to fail consistently after applying one of those recent updates. In conjunction with the updates we applied registry entry changes in an attempt to get SQL Server to support TLS 1.2+ with Microsoft Office Email on Smtp.office365.com port 587.

The error we received from the failed emails was very vague:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2022-01-31T06:18:01). Exception Message: Cannot send mails to mail server. (Failure sending mail.).

I used this simple script to test sending emails:

declare @rc int
  exec @rc = msdb.dbo.sp_send_dbmail
      @profile_name = N'SQLSend',
      @recipients         =  'myemail@mail.com',
      @subject    = N'*** Test by me ***',
      @body     = 'email body'

I wrote a C# .Net program to see if I could get more error detail. I didn’t know what version of .Net Framework was on the server so I compiled against .Net 2.0 and ran my program and got the same error. Then I compiled my program on .Net 4.8 and got a different error. What I learned was that the newer version of the .Net framework (anything 4.0 or higher) provides a more detailed error message.

So my next task was to figure out if SQL Server is using the .Net Framework to send the Email (which I think it is) and how to tell SQL Server to use a newer version of the .Net Framework.

SQL Server uses an executable named DatabaseMail.exe to send the email. That program will exist in a folder named something like: D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn

There program is a .Net program and therefore includes a .Config file. Or, I should say, it “can” include a config file. I think what happened is that .config file for our DatabaseMail.exe got deleted when an SQL Server update was installed (as mentioned in this article: https://support.microsoft.com/en-us/topic/kb3186435-fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-does-not-have-the-net-framework-3-5-installed-or-stops-working-after-applying-sql-server-update-3480beb6-1329-74d6-0f3a-e8e3d893326c

So I used notepad to create a new file named databasemail.exe.config in the same folder, and I put this content into the file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DatabaseServerName" value="." />
    <add key="DatabaseName" value="msdb" />
  </appSettings>
  <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

By telling DatabaseMail.exe to use .Net Framework 4.5, we were able to get a more detailed error message from the failed emails. In our case, the error we got by using .Net Framework 4.5 is shown below. Our belief is that one of the SQL Server updates somehow altered the existing configured password of the hashing or encryption algorithm used for it. We simply re-entered our password in DatabaseMail configuration and our email started working again.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2022-02-01T12:47:47). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.57 Client not authenticated to send mail. Error: 535 5.7.139 Authentication unsuccessful, the user credentials were incorrect.

Posted in I.T., SQL Server | 1 Comment »

How To Reduce The Connection Pools For a .Net Web Site Hitting Multiple Databases

Posted by robkraft on May 9, 2020

Microsoft’s connection pooling built in to the .Net Framework greatly improves performance by allowing hundreds of database calls to share the same pool of connections used to connect to the database. The .Net Framework creates a new pool of connections for each unique connection string. This works fantastic when your web application connects to a single database and all your connections use the same connection string. It even works well if you have two or three different connection strings, but for each unique connection string value, the .Net Framework creates a new connection pool. If your web site has dozens of different databases to connect to, you may find your application creates dozens of connection pools which begins to consume a lot of resources on both the web server and SQL Server.

There is a fairly easy way to eliminate this problem, and that is to use the ChangeDatabase method on the Connection object.

Our web application was connecting to one of fifty different databases that all reside on the same instance of SQL Server. Each of our clients has their own database. But our application was creating fifty different connection pools. We were able to have all the clients share a single pool of connections by first connecting to a neutral database, then redirecting the connection to the desired database.

We created a new database on the server, called Central, then we had all the connections to the database first connect to Central as the Initial Catalog. The next step was to call ChangeDatabase to switch to the desired database. This technique did not create a new connection to SQL Server and did not create a new connection pool on the .Net client. Microsoft documentation mentions this technique here: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases , but their example code does not show the ChangeDatabase method, which is more efficient than their example.

In a nutshell, you can do this:

connection.Open();
connection.ChangeDatabase(“Client1”);

I created a simple Windows Form App to test the idea.  The test app runs a query to return the name of the current database it is in.  Also, on the SQL Server I run a query to see all the connections that exist.  Doing this I was able to prove that using ChangeDatabase did not create a new connection to SQL Server and my .Net SQL Connection Object was pointing at the correct desired database.  Sample code, query, and output is below:

Sample C# .Net Forms App:

using System;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SQLConnectionTest
{
	public partial class Form1 : Form
	{
		//https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#pool-fragmentation-due-to-many-databases
		private void button1_Click(object sender, EventArgs e)
		{
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=model;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=msdb;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{ 
				connection.Open();
				connection.ChangeDatabase("TempDb");
				RunSQL(connection, "select DB_NAME()");
			}

			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				RunSQL(connection, "select DB_NAME()");
			}
			using (SqlConnection connection = new SqlConnection(@"Server=.\;Database=master;Trusted_Connection = yes"))
			{
				connection.Open();
				connection.ChangeDatabase("TempDb");
				RunSQL(connection, "select DB_NAME()");
				connection.ChangeDatabase("model");
				RunSQL(connection, "select DB_NAME()");
			}
		}

		public static void RunSQL(SqlConnection connection, string queryString)
		{
			SqlCommand command = new SqlCommand(queryString, connection);
			try
			{
				SqlDataReader reader = command.ExecuteReader(); 
				if (reader != null)
				{
					while (reader.Read())
					{
						Console.WriteLine("\t{0}", reader[0]);
					}
					reader.Close();
				}
			}
			catch (Exception ex){Console.WriteLine(ex.Message);}
		}
		public Form1()
		{
			InitializeComponent();
		}


	}
}

Output from Running the App written to console:

master
model
msdb
master
tempdb
master
tempdb
model

Query to see connections on SQL Server:

CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’) and ProgramName like ‘.net%’
DROP TABLE #TmpLog
go

Results of running query on SQL Server:

Results1

Posted in Coding, SQL Server | Leave a Comment »

How To Filter SP_WHO to Just The Connections You Care About

Posted by robkraft on May 8, 2020

You can run sp_who, or better yet, sp_who2 to quickly get some information about all the current connections to your SQL Server, but for those of us that run it often you probably prefer that it not show all the internal processes connected to the engine.  You can filter the the results of the stored procs by dumping the query results into a temp table and filtering on the temp table.  Something like this:

CREATE TABLE #TmpLog

(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),

CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)

INSERT INTO #TmpLog

EXEC sp_who2

SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName

FROM #TmpLog

where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)

DROP TABLE #TmpLog

Better yet, you can create your own stored proc in the master database then you can run your stored proc with a short command when desired:

create proc sp_whoMine
as
CREATE TABLE #TmpLog
(SPID int, Status VARCHAR(150), Login varchar(100), HostName VARCHAR(150), BlkBy varchar(30), DBName varchar(60), Command varchar(500),
CPUTime int, DiskIO int, LastBatch varchar(20), ProgramName varchar(200), SPID2 int, requestid int)
INSERT INTO #TmpLog
EXEC sp_who2
SELECT SPID, Status, Login, HostName, BlkBy, DBName, Command, ProgramName
FROM #TmpLog
where STATUS not in (‘BACKGROUND’) and Command not in (‘TASK MANAGER’)
DROP TABLE #TmpLog
go

Posted in SQL Server | Leave a Comment »

A C# .Net Dialog For Connecting to SQL Server

Posted by robkraft on May 7, 2020

I created a Windows C# .Net Form and made it an example for anyone that wants to create a prompt for logging into SQL Server that looks at behaves like SQL Server Management Studio and other Microsoft products.

The code can be found here: https://github.com/RobKraft/SQLServerLoginTemplate

The code does not “share” the saved credentials with the Microsoft products. It stores those credentials in User Specific Settings for the specific application. Passwords are encrypted using the Windows Machine key (DPAPI), therefore this code will not work if used on Linux.

Posted in Coding, SQL Server | Leave a Comment »

SQL to Find Who Is Trying To Hack The SQL Server You Exposed To The Internet

Posted by robkraft on May 5, 2020

If you are running a SQL Server exposed to the Internet on the default port 1433 then hackers are probably try to hack in to it.  If the SQL Server is running on a different port it is less likely, but it could still be happening.  If you exposed a SQL Server to the world the hackers on the Internet will find it and will launch repeated login attempts against the ‘sa’ account.

That is a good reason for disabling the ‘sa’ account.

That is also a good reason for renaming the ‘sa’ account.

Most SQL Server admins record the Failed Login Attempts in the SQL Server Error Log.  If you have this option turned on, which is recommended, you may see entries like this:

Login failed for user ‘sa’. Reason: Could not find a login matching the name provided. [CLIENT: 113.64.210.68]

You can write an SQL to read the SQL Server ErrorLog and extract all the IP addresses that are attempting to login to your server using the ‘sa’ account.  You can then provide that list to your firewall to blacklist them, although a whitelist of known good addresses is a much better approach.  After you blacklist those IP Addresses, come back the next day and check your logs again because you will probably find a whole bunch of new IP addresses doing the same thing.  This game of ‘whack-a-hacker’ can be played for a long time before the hackers run out of IP addresses.  By the way, if you are going to try to blacklist the bad IP addresses, I recommend blocking entire blocks like 113.64.*.*, not a single IP Address at a time.

Here is an SQL to get you started with reading and filtering your SQL Server Error Logs.  Notice this script only looks for failed login attempts using the ‘sa’ account.  If you look at your logs, you will find failed login attempts for other accounts too.  Happy Hunting.

CREATE TABLE #TmpLog
(LogDate datetime, ProcessInfo VARCHAR(150), Text VARCHAR(max))
INSERT INTO #TmpLog
EXEC sp_readerrorlog
SELECT replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ',''), 
count(*)
FROM #TmpLog where Text like '%Client%' and Text like '%''sa''%' group by
replace(Text,'Login failed for user ''sa''. Reason: Could not find a login matching the name provided. ','')
having count(*)>10
order by 2 desc
DROP TABLE #TmpLog

 

 

Posted in Security, SQL Server | Leave a Comment »

SQL Server’s sp_executesql Does Not Protect You from SQL Injection

Posted by robkraft on August 18, 2019

Many coders of SQL have learned we can dynamically construct SQL statements inside of stored procedures and then execute the constructed SQL.  In Microsoft’s SQL Server product there are two commands we can choose for running the constructed SQL:

  • EXEC (EXEC is an alias for EXECUTE, both do the same thing).
  • sp_executesql.

We SQL Server “experts” often advise coders to use sp_executesql instead of EXEC when running dynamically constructed SQL statements to reduce the risk of SQL Injection, and this is good advice.  But it is not the use of sp_executesql that prevents SQL injection, it is the use of parameters with sp_executesql that helps protect against SQL Injection.  You can still construct SQL dynamically and run that SQL using sp_executesql and be affected by a SQL Injection attack.

If you use parameters to substitute all the values in the SQL and then use sp_executesql you have probably eliminated the SQL Injection risk; but as a developer this means you may be unable to dynamically construct the SQL you want to run.

When you use sp_executesql parameters correctly, you can only replace data values in your SQL statement with values from parameters, not parts of the SQL itself.  Thus we can do this to pass in a value for the UserName column:

declare @sql nvarchar(500)
declare @dynvalue nvarchar(50)
select @dynvalue=’testuser’
SET @sql = N’SELECT * FROM appusers WHERE UserName = @p1′;
EXEC sp_executesql @sql, N’@p1 nvarchar(50)’, @dynvalue

But the following code will return an error when trying to pass in the name of the table:

declare @sql nvarchar(500)
declare @dynvalue nvarchar(50)
select @dynvalue=’appusers’
SET @sql = N’SELECT * FROM @p1′;
EXEC sp_executesql @sql, N’@p1 nvarchar(50)’, @dynvalue

Msg 1087, Level 16, State 1, Line 1
Must declare the table variable “@p1”.

If you are dynamically constructing SQL, and you are changing parts of the SQL syntax other than the value of variables, you need to manually write the code yourself to test for the risk of SQL injection in those pieces of the SQL.  This is difficult to do and probably best handled by the application calling the stored procedure.  I recommend that the calling program do the following at a minimum before calling a stored procedure that dynamically constructs SQL:

  1. Validate the length of the parameter. Don’t allow input longer than the maximum length expected.  If the stored procedure allows a column to be passed in that is used for sorting in an ORDER BY clause, and all of your column names are less than or equal to 10 characters in length, then make sure that the length of the parameter passed in does not exceed 10 characters.
  2. Don’t allow a single single quote, make sure to replace a single single quote with two single quotes.
  3. Don’t allow other special characters or even commands such as a semicolon or the UNION keyword or two hyphens that represent a comment in SQL.
  4. Don’t allow ASCII values greater than 255.

That short list is not sufficient to prevent all SQL Injection attacks, but it will block a lot of them and gives you an idea of the challenge involved in preventing SQL Injection attacks from being effective.

If you would like to see for yourself how the EXEC and sp_executesql statements behave I have provided a script you can use to get started with.  Related to this article, the most important query to understand is the last one because it shows a case of SQL injection even though the dynamically generated SQL is ran using sp_executesql.

–1. Create tables and add rows
DROP TABLE InjectionExample
GO
DROP TABLE Users
GO
CREATE TABLE InjectionExample ( MyData varchar (500) NULL)
GO
INSERT INTO InjectionExample VALUES(‘the expecteddata exists’), (‘data only returned via sql injection’)
GO
CREATE TABLE Users( username varchar(50) NULL,[password] varchar(50) NULL)
go
INSERT INTO Users VALUES (‘user1′,’password1’), (‘user2′,’password2’), (‘user3′,’password3’)
GO
–2. Run a test using EXEC with data the programmer expects
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ‘expecteddata’
select @sql = ‘SELECT * FROM InjectionExample WHERE MyData LIKE ”%’ + @p1 + ‘%”’
exec (@sql)–returns 1 row as expected
GO

–3. Run a test using EXEC with data the hacker used for sql injection
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ”’ or 1 = 1–‘
select @sql = ‘SELECT * FROM InjectionExample WHERE MyData LIKE ”%’ + @p1 + ‘%”’
exec (@sql)–returns all rows – vulnerable to sql injection
GO

–4. Run a test using sp_executeSQL to prevent this SQL Injection
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
select @p1 = ‘expecteddata’
select @sql = N’select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%”’
exec sp_executesql @sql, N’@param1 varchar(50)’, @p1
GO

–5. Run a test using sp_executeSQL to prevent this SQL Injection – hacker data returns no results
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
declare @pOrd nvarchar(50)
select @p1 = ”’ or 1 = 1–‘
set @pOrd = ‘MyData’
select @sql = N’select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%” order by ‘ + @pOrd
exec sp_executesql @sql, N’@param1 varchar(50)’, @p1
GO

–6. But sp_executesql does not protect against all sql injection!
–In this case, sql is injected into the @pOrd variable to pull data from another table
declare @sql nvarchar(500)
declare @p1 nvarchar(50)
declare @pOrd nvarchar(50)
set @p1 = ‘expecteddata’
set @pOrd = ‘MyData; SELECT * FROM Users’
select @sql = ‘select * from InjectionExample WHERE MyData LIKE ”%” + @param1 + ”%” order by ‘ +@pOrd
exec sp_executesql @sql, N’@param1 nvarchar(50)’, @p1

 

 

Posted in CodeProject, Security, SQL Server | Leave a Comment »

Microsoft SQL Server Violates the Commutative Law of Basic Math

Posted by robkraft on April 16, 2015

I love SQL Server.  I’ve been a SQL Server expert for decades, but just a few days ago I found a bug in SQL Server when performing simple math calculations.

These two queries yield different results:

  • select 1.0 * 3/2
  • select 3/2 * 1.0

The first query returns 1.5, and the second query returns 1.0.  Different results violates the Commutative rule of math that tells us the order of operands should not matter.

I understand why this is happening.  SQL Server looks at the data type of the first argument and rounds the results of calculations to that data type.  So in the first query, 1.0 is a float (aka double); but in the second query 3 is an int (aka integer) with no decimal places so 3/2 rounded to fit in an integer is 1, not 1.5.  But just because we can explain it does not mean that it is acceptable.

This problem affects real code.  The problem occurs not just with values, but when doing similar calculations with columns in tables.

  • create table CommTest (int1 int, int2 int, dbl1 float)
    insert into CommTest values(3,2,1.0)
    select dbl1 * int1/int2 from CommTest
    select int1/int2 * dbl1 from CommTest

The first select in this query will return 1.5, and the second select will return 1.

The fix is to cast the first variable as a float “select cast(int1 as float)/int2 * dbl1…”, but that is challenging to remember and difficult to apply if you have code generating SQL for you.  The better solution is probably to do all math in the program calling the database; at least if SQL Server is your DBMS.  Though this is not easy when you just have a reporting tool like SSRS for your front end.

Until Microsoft fixes this, which I doubt they ever will, developers just need to be extra careful.

This problem does not exist in other database management systems.  At least I know it does not exist in Microsoft Access nor Oracle.  For reference, I tested this on SQL Server 2014.

Posted in SQL Server | Tagged: | 2 Comments »

How to move data from a DBISAM database into SQL Server

Posted by robkraft on December 15, 2014

In December 2014 I acquired a task to retrieve all the data from an application storing the data in a DBMS format known as DBISAM.  DBISAM stores data for each table in separate files.  I discovered that each table consisted of either two or three files, a .dat file with the data and a .idx file for indexes and optionally a .blb for blog storage of images or large text blocks.

The first, and just about only, place you can go on the Internet for things related to DBISAM is www.elevatesoft.com.  You can download some free tools to assist with this task, and there are occasionally used forums where you can post questions.  The staff responded quickly to my one post.  From the downloads section you can download a package of tools called DBISAM-ADD.  Get the most current version.  The robust installer will place some programs on your computer (I was running Windows 8.1 and the tools worked fine).

The helpful tool is called “Database System Utility”, which is a program named dbsys.exe installed to C:\Program Files (x86)\DBISAM Additional\Version 4\utils\dbsys\win32.  With this program you can select any table to open and run queries against it, including data modifications.  You can also use the Export option to export the data to a .csv file.  However, blob data, including Memo fields will not be included in the exported .csv file.  So I do not recommend this option.

DBISAM Export

Getting the Data from Memo Fields Requires ODBC

ElevateSoft provides an ODBC driver on the same download page for the other DBISAM tools.  After running the installer for the ODBC driver, you need to open “ODBC Data Source Administrator (32-bit)” and add a “System DSN”.  I think you can use all the defaults if you are connecting to DBISAM files on your local computer.   My DBISAM files all have a password to protect them, so I needed to enter that password on the last tab/prompt of the ODBC configure wizard.  Then I was ready to use that ODBC driver with SSIS.

SSIS requires a standard edition or higher of SQL Server.  You cannot use it with SQL Server Express Edition.  Although I have installed a standard edition of SQL Server 2014, the most current SSIS tools are for SQL Server 2012.  And even though they use Visual Studio, I don’t think you can open Visual Studio directly, or at least that has not worked for me.  To get into SSIS, I open “SQL Server Data Tools for Visual Studio 2012”.

The secret to importing data using ODBC from DBISAM is to use the SSIS Import and Export Wizard.  I could not figure out how to do this import without the wizard.  Only the wizard allowed me to pick the ODBC connection I had created to the DBISAM Files.  See the picture below for an example of the Source configuration for the wizard.

Configure Data Source for DBISAM

For the destination you can pick your SQL Server database, then you should be prompted with a list of tables to include.  I had over 300 tables to import so I ran the wizard about 10 times and broke the 300 tables into smaller groups so that I could rerun the smaller chunks later.

The wizard does seem to have a problem with date and time fields coming from DBISAM and it will set their datatype to a number for mapping as in the screen shot below.  Simply change that to a datetime, or smalldatetime if you prefer, and you should be ready to go.

Edit Mappings for DBISAM

If you plan to run this multiple times, which you probably want to write a script that runs before your packages to delete the tables you created if they currently exist.  I recommend using the “Execute SQL Task” on the “Control Flow” tab of your package and having it run your “Data Flow Tasks” upon completion.

I also had some imports fail because the dates entered by the users into the DBISAM based system were outside the range of valid dates that SQL Server allows.  I found data with dates like ’12/31/0210′ instead of ’12/31/2010′.  I used the tools in the DBISAM utility to correct that data as shown here:

Run A Data Modification Query in DBISAM

Select,   “New Query” from the File menu and you can type in a query.  Dates must be surrounded by single quotes and be in yyyy-mm-dd format.  After correcting the data I was able to successfully import all the data.

Even the blob data such as images came across successfully into SQL Server blob datatypes.

Send me a message if you have any trouble with this.

Posted in Coding, SQL Server | Tagged: | 8 Comments »