Rob Kraft's Software Development Blog

Software Development Insights

Archive for the ‘SQL Server’ Category

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
CREATE TABLE InjectionExample ( MyData varchar (500) NULL)
INSERT INTO InjectionExample VALUES(‘the expecteddata exists’), (‘data only returned via sql injection’)
CREATE TABLE Users( username varchar(50) NULL,[password] varchar(50) NULL)
INSERT INTO Users VALUES (‘user1′,’password1’), (‘user2′,’password2’), (‘user3′,’password3’)
–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

–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

–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

–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

–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  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.


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 »

The Correct Way to Write A SQL Server Trigger That Modifies the Record Just Inserted

Posted by robkraft on October 31, 2014

It is easy to write a trigger that has unintended consequences, which is why I recommend you always test your triggers for two things:

  1. Did the trigger update the records I expected it to correctly?
  2. Did the trigger update records I did not expect it to?

Here is an example of a trigger we had that was incorrect.  We discovered it because it was taking so long to run the insert query that the query was timing out.  And it was taking so long because it was updating all the records in the table, not just the one we wanted it to modify.

In this example, we want to update the OrderDetails date to be equal to the OrderDate from the parent table, but only for orders with type=200 and only when new order details are inserted.  Run this SQL to create the tables and add some test data.

drop Table Orders
drop table Orderdetails
Create Table Orders (OrderID int, OrderDate datetime, OrderType int)
Create Table OrderDetails (DetailID int, parentOrderID int, DetailDate datetime)
insert into orders values (1,’10/25/2014′,100)
insert into orders values (2,’10/27/2014′,200)
insert into orders values (3,’10/28/2014′,100)
insert into orders values (4,’10/29/2014′,200)
insert into OrderDetails values (10, 1, ’10/25/2015′)
insert into OrderDetails values (11, 1, ’10/26/2015′)
insert into OrderDetails values (14, 2, ’10/25/2015′)
insert into OrderDetails values (15, 2, ’10/26/2015′)
insert into OrderDetails values (16, 3, ’10/25/2015′)
insert into OrderDetails values (17, 3, ’10/26/2015′)
insert into OrderDetails values (18, 4, ’10/25/2015′)
insert into OrderDetails values (19, 4, ’10/26/2015′)

Then run this script to create the incorrect stored procedure:

CREATE TRIGGER dbo.MyFirstTriggerThatDoesntWork
ON OrderDetails
UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
INSERTED I ON Orders.OrderID = I.parentOrderID
WHERE (Orders.OrderType = 200)

Now let’s run an INSERT and check the results:

insert into OrderDetails (DetailID, parentOrderID) values (20, 4)

The messages displayed look like the following, and that message with “9 row(s) affected” is a good indicator that something went wrong:

(1 row(s) affected)
(1 row(s) affected)
(9 row(s) affected)
(1 row(s) affected)

Looking at all of our data, we can see that every record got updated to the 10/29 date:

select * from OrderDetails
10 1 2014-10-29 00:00:00.000
11 1 2014-10-29 00:00:00.000
14 2 2014-10-29 00:00:00.000
15 2 2014-10-29 00:00:00.000
16 3 2014-10-29 00:00:00.000
17 3 2014-10-29 00:00:00.000
18 4 2014-10-29 00:00:00.000
19 4 2014-10-29 00:00:00.000
20 4 2014-10-29 00:00:00.000

That is not what we wanted.  Here is the correct way to write this trigger. Do this after running the script above to drop and recreate the tables.

CREATE TRIGGER dbo.MySecondTriggerThatDoesWork
ON OrderDetails
UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
INSERTED I ON Orders.OrderID = I.parentOrderID INNER JOIN OrderDetails d on d.DetailID = I.DetailID
WHERE (Orders.OrderType = 200)

If we run the insert again and look at the output messages we will see that only 1 record was affected and we can see the correct output in our results:

insert into OrderDetails (DetailID, parentOrderID) values (20, 4)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
select * from OrderDetails
10 1 2015-10-25 00:00:00.000
11 1 2015-10-26 00:00:00.000
14 2 2015-10-25 00:00:00.000
15 2 2015-10-26 00:00:00.000
16 3 2015-10-25 00:00:00.000
17 3 2015-10-26 00:00:00.000
18 4 2015-10-25 00:00:00.000
19 4 2015-10-26 00:00:00.000
20 4 2014-10-29 00:00:00.000

I emboldened the line of code in the trigger that made the difference.  In the first trigger, we are updating the OrderDetails table with the result of a join between the Inserted table and the Orders table.  So basically, that says “run a query that joins the Orders table with the Inserted table, and apply the results of that join to EVERY record in OrderDetails”.  It is applied to EVERY record in OrderDetails because we did not apply any criteria to limit which records on OrderDetails are affected.

UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN INSERTED I ON Orders.OrderID = I.parentOrderID

The fix was include a join to the table we are updating (OrderDetails) and limit which records it is going to affect by joining the OrderDetails table to the Inserted table.

UPDATE OrderDetails
SET DetailDate=Orders.OrderDate
FROM Orders INNER JOIN INSERTED I ON Orders.OrderID = I.parentOrderID INNER JOIN OrderDetails d on d.DetailID = I.DetailID

In the example above, the Inserted table would have 1 record in it, with the values of DetailID=20 and parentOrderID=4.  Our trigger would update a record in OrderDetails that had the same DetailID of 20 and set the DetailDate equal to the OrderDate of the Order we were joined to.

Posted in SQL Server, Uncategorized | Leave a Comment »

Use “NETWORK SERVICE” to Start SQL Server Service in 2014 on Local Computer

Posted by robkraft on May 14, 2014

I had a really hard time getting the SQL Server 2014 install to succeed.  I spent days on it.  I uninstalled all my earlier SQL Server Editions, which is a tedious process because you need to uninstall a lot of different components in specific order.  The SQL Server 2014 install then succeeded, but the SQL Server service would not start.

Event viewer showed this error when trying to start SQL Server:
Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.

I tried configuring the SQL Server Service to start using a different windows account, but that did not work either, although I was on the right track.  I did need to change the account used by SQL Server, but I needed to do it during the install process.  So, I uninstalled all the SQL Server components again, then ran the install again.

Apparently the default SQL Server service account is not working on my Windows 7 machine, so I need to pick a different account, and that account needed to be “Network Service”.  I tried several other promising looking accounts such as “Administrator”, “LOCAL SERVICE”, and “SERVICE”, but only “NETWORK SERVICE” from my local machine security group worked.

Don’t do this on production servers, it is not the best security choice.

Select NETWORK SERVICE to get SQL Server 2014 to work

Select NETWORK SERVICE to get SQL Server 2014 to work

Posted in SQL Server | Leave a Comment »

My Least Favorite New Feature in SQL Server 2014 – No CTRL+E

Posted by robkraft on April 4, 2014

Since as long as I can remember, perhaps back to SQL Server 6.5, I’ve been using CTRL+E to “execute” queries inside SQL Server Management Studio.  I knew that Microsoft warned they were going to eliminate that keystroke, and they finally have in SQL Server 2014.  Now the only keystroke for running queries appears to be F5.  I consider this a productivity setback because to hit F5, I have to lift my palm off of its resting position by the keyboard, then relocate my fingers back on the keyboard correctly to continue typing.  I did not need to do this when using CTRL+E.

Is it possible to complain enough to get a response from Microsoft?  I’m not asking for CTRL+E to come back, but I would like a key combination that does not require me to take my hands of the keyboard to use it.  I like running queries!

Posted in SQL Server | 1 Comment »

SP_WhoIsActive is a Superior Alternative to sp_who on SQL Server

Posted by robkraft on August 13, 2012

I recently learned about the sp_WhoIsActive stored procedure written and maintained primarily by Adam Mechanic.  For DBAs like myself who have always wanted something better than sp_who and sp_who2 to diagnose the active connections on your SQL Server, this is a tool for you.  You can download it at and you can learn all about it through Adam’s many blog posts.  I recommend starting with this one:


Find more tool gems like this at


Posted in Free tools, SQL Server | Leave a Comment »

Use a SQL script to generate well formatted stored procedures in SQL Server

Posted by robkraft on February 9, 2012

Occasionally you realize that you desire to create a lot of stored procedures, and that the information to build each stored procedure is contained within the database.  But how can you best generate the code from the data?  Well, assuming you like SQL, you can probably use SQL to generated your SQL.

Here is an example of doing just that, along with a few tips to make the code you generate human-readable.

  • Use char(13) + char(10) to wrap your output to the next line.
  • Use char(9) to indent by one tab.
  • Create one line of output for each line of your code-generating SQL script.
  • First write the query that obtains the values you need for code generation without generating the code to make sure you have the correct result set.
  • Generate your output to text, then copy the output into a new query window and it should look great!

This example creates a stored procedure for each table in your database.  Each stored procedure will query the first integer column in the table and return the first row with an integer value greater than the value you passed in.  This particular script will probably not be useful to you, and you may not like the way I formatted my output, but my only intent is to provide you a nice starting point for doing something like this to meet your own needs.  This should work on most versions of SQL Server.  Enjoy!

'CREATE PROCEDURE mysp_' + i.TABLE_NAME + 'FIRSTCOL ' + char(13) + char(10)
+ '(' + char(13) + char(10)
+ char(9) + '@' + i.COLUMN_NAME + ' int ' + char(13) + char(10)
+ ')' + char(13) + char(10)
+ 'AS' + char(13) + char(10)
+ 'BEGIN' + char(13) + char(10)
+ char(9) + 'SET NOCOUNT ON' + char(13) + char(10)
+ char(9) + 'DECLARE @Err int' + char(13) + char(10)
+ '/*Comment-Begin*/' + char(13) + char(10)
+ 'SELECT ' + i.COLUMN_NAME + char(13) + char(10)
+ char(9) + 'FROM ' + char(13) + char(10)
+ char(9) + char(9) + i.TABLE_NAME + char(13) + char(10)
+ ' WHERE ' + char(13) + char(10)
+ char(9) + char(9) + i.COLUMN_NAME + ' > @' + i.COLUMN_NAME + char(13) + char(10)
+ '/*Comment-End*/' + char(13) + char(10)
+ char(9) + 'SET @Err = @@Error' + char(13) + char(10)
+ char(9) + 'RETURN @Err' + char(13) + char(10)
+ 'END' + char(13) + char(10)
+ 'GO' + char(13) + char(10)
+ char(13) + char(10)
+ 'GRANT EXEC ON ' + 'mysp_' + i.TABLE_NAME + 'FIRSTCOL ' + 'TO everyone' + char(13) + char(10)
+ 'GO' + char(13) + char(10)
+ char(13) + char(10)
+ char(13) + char(10)

Posted in CodeProject, Coding, SQL Server | 4 Comments »

Send SQL Server query results as nicely formatted emails in Outlook

Posted by robkraft on February 4, 2012

I find myself sending the results of a lot of SQL queries I run in SQL Server Management Studio via e-mail to co-workers.  The problem is, that the email content often looks terrible when I paste it into outlook from SQL Server Management Studio.

However, I have found that I can make it look better using the following steps.

  1. Run the query and let the output results go to grid (the default for most people).
  2. Right-click in the results and select ‘Copy with Headers’.
  3. Open Excel and paste the results.
  4. Click in the upper left corner of the Excel grid to select all rows and columns.
  5. Double-click between any two columns (A and B) to resize the widths of all columns.
  6. Select the “Center Text” alignment so that all column values are centered, rather than the default left-justified.
  7. Now copy all the contents of the Excel spreadsheet and paste the results into the body of your email in Outlook.

I find the results usually look pretty decent.  Can you provide me a way to do this that is easier?

Posted in Process, SQL Server | Leave a Comment »

Find Stored Procedures that are not using all the Parameters Passed into them. SQL Server.

Posted by robkraft on December 10, 2011

This last week we discovered a bug in a stored procedure. We were passing a parameter into a stored procedure used to do an insert, but the value was not used in the actual insert statement, and thus the value was not stored in the database table. The problem was easy to fix, but as with all bugs, I ask if there is a way we can prevent the bug from recurring. We came up with an SQL statement that we can run to identify all the stored procedures that have unused parameters into them. We then set up a unit test to run every night to let us know if any of our stored procedures have this flaw. Here is the stored procedure that should work on any version of SQL Server from 2005 on up.


The clever piece of this query is that we use the Information_Schema.Parameters to get a list of all the parameters in each stored procedure, and then we replace all occurrences of the parameter name in the stored procedure with an empty length string. If the length of the stored procedure prior to our substitution and subtracting the length of the parameter name just once is equal to zero, then we know the parameter only occurs once, in the input, and that it is not used within the body of the stored procedure.

Note, you may want to include an additional clause in the where clause if you want to exclude some of the system stored procedures.

Posted in SQL Server | Leave a Comment »