How to pass special characters from external application via batch file to SQL Script

• Saturday, July 21, 2018

• By Jovica Turcinovic

Sounds weird? Yes, it is. Software industry requires us to play a role of mediator to make different worlds talk to each other in the most proper/efficient way.

As usual, when different parts of the system are supposed be integrated, we have to turn on all the lamps in our heads in order to find the best solution considering all possible consequences.

So, my goal is to pass a string with special characters. Let’s define the string as:

s3TV_<4~%fE!6gM^

If I try to simply define a variable in batch file and print it out back to the console, I'll lose some special chars. Batch file example:

setlocal EnableDelayedExpansion

set test="s3TV_<4~%fE!6gM^"
echo !test!

Huuuuh, the result is wrong :(

s3TV_<4~fE6gM

The batch file didn't throw any error, but failed to keep the passed string in its initial form.

One solution would be to escape every single special character by properly consulting the documentation/online resources:http://www.robvanderwoude.com/escapechars.php

The above state that I have to adapt my string as follows:

s3TV_<4~%fE^!6gM^^

Once I apply that change, the string will be displayed properly. So, it means that the external application has to prepare the string and be aware of all special cases.This is not the end of the story. On the other hand, if the batch file should pass it somewhere else, like an SQL Server script in our case - we need to deal with requirements dictated by SQL Server. There is an additional set of reserved chars, rules, etc.

These modules are tightly coupled and I'd like to avoid that.

In case we need to deal with printable asciis or extended ascii codes, why wouldn’t we introduce the exchange message format using the ascii codes?

This means that, instead of sending s3TV_<4~%fE!6gM^, why don't we send the delimited ascii codes of the original string?

The s3TV_<4~%fE!6gM^ will become:

115-51-84-86-95-60-52-126-37-102-69-33-54-103-77-94

Isn't this a phone call to Mars? :)

The combination of integers and a predefined delimiter help us to avoid the problem with special chars. All participants in the system which exchange strings have to be familiar with the format of the passed string and how it can be handled.

All of our modules have to use the same language and know how to handle it on its own.

Example

Suppose we need to create a new login on SQL Server if it doesn't exist by calling the login_create.bat file. In order to execute it properly, we will assume that the currently logged in windows user has the privilege to execute the sqlcmd (SQL Instance has enabled Windows SQL Authentication).

We may assume that the caller of the login_create.bat file knows that the string/password should be sent in the correct format ([int]-[int]). Another approach would be to have an encrypted string (in order to protect the password from stealing). In that case, we need to decrypt it and convert it to [int]-[int] format again on the fly.

Let’s simplify and assume that we need to pass the following info to the batch file:

-dbServerInstance -newLoginName -newLoginPassword [in [ascii]-[ascii] format] login_create.bat

First, let’s fetch the parameters:

setlocal EnableDelayedExpansion

set sqlScriptPlaceholders=2
set errorExitCode=0
set placeholder[1]="<DB_LOGIN_NAME>"
set placeholder[2]="<DB_LOGIN_PASSWORD>"

set placeholderswithvalues=""

:paramReadLoop
IF NOT "%1"=="" (
    IF "%1"=="-newLoginName" (
		SET replace[1]=%2
        SHIFT
    )
	IF "%1"=="-newLoginPassword" (
        SET replace[2]=%2
        SHIFT
    )
	IF "%1"=="-dbServerInstance" (
		SET dbServerInstance=%2
		SHIFT
	)
    SHIFT
    GOTO :paramReadLoop
)

:: ------------------------------------------------------
:: Prepare SQL Script and execute
:: ------------------------------------------------------

copy "create_login.sql" "run_create_login.sql"

FOR /L %%A IN (1,1,!sqlScriptPlaceholders!) DO (
	set search=!placeholder[%%A]!	
	set replace=!replace[%%A]!	
	call:searchAndReplace !search!,"!replace!"
)

sqlcmd -S !dbServerInstance! -E -i run_create_login.sql > run_create_login.log

IF EXIST run_create_login.sql del /F run_create_login.sql

EXIT /B 0
goto :EOF

:searchAndReplace
for /f "delims=" %%i in ('type "run_create_login.sql" ^& break ^> "run_create_login.sql" ') do (
	set "line=%%i"
	set "line=!line:%~1=%~2!"
	>>"run_create_login.sql" echo(!line!
)
goto:eof

Check out the initial create_login.sql script in the listing below

USE [master]

DECLARE @pass NVARCHAR(500)
DECLARE @sqlStmt nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'<DB_LOGIN_NAME>')
BEGIN
	SELECT @pass = dbo.fn_AsciiListToSqlChars('<DB_LOGIN_PASSWORD>', '-')
	
	SELECT @sqlStmt = N'SELECT @retvalOUT = ' + @pass;
	SET @ParmDefinition = N'@retvalOUT NVARCHAR(500) OUTPUT';

	EXEC sp_executesql @sqlStmt, @ParmDefinition, @retvalOUT=@pass OUTPUT;
		
	SET @sqlCommand = '
	CREATE LOGIN [DB_LOGIN_NAME] WITH
		 PASSWORD=N''' + @pass + ''',
		 DEFAULT_DATABASE=[master],
		 DEFAULT_LANGUAGE=[us_english],
		 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'

	EXEC sp_executesql @sqlCommand
END

You may have noticed that I used the dbo.fn_AsciiListToSqlChars function. It doesn't exist and needs to be provided.

This is a specific implementation of the conversion between our unique representation of string to the actual value.

Furthermore, SQL Server should know its own restrictions (like escaping which may be incorporated here instead of forcing to know the restrictions of the specific component which is passing data).

Here is a code snippet for the fn_AsciiListToSqlChars SQL function:

USE [master]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'fn_AsciiListToSqlChars') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION fn_AsciiListToSqlChars
GO

CREATE FUNCTION [dbo].[fn_AsciiListToSqlChars]
(
    @stringToSplit NVARCHAR(4000),
	@delimiter CHAR(1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	 DECLARE @name NVARCHAR(255)
	 DECLARE @pos INT
	 DECLARE @Result VARCHAR(MAX);

	 SET @Result = ''

	 WHILE CHARINDEX(@delimiter, @stringToSplit) > 0
	 BEGIN
		SELECT @pos  = CHARINDEX(@delimiter, @stringToSplit)  
		SELECT @name = SUBSTRING(@stringToSplit, 1, @pos - 1)

		IF(@Result <> '')
			SET @Result = @Result + ' + '

		SET @Result = @Result + 'CHAR(' + @name + ')'
		
		SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) - @pos)
	 END

	 SELECT @name = SUBSTRING(@stringToSplit, 1, @pos)
	 SET @Result = @Result + '+ CHAR(' + @name + ')'
	 
	 -- Here the specific limitations and escaping can be applied.

	 RETURN REPLACE(@Result, CHAR(9), '')
END
GO

This is a simplified solution and doesn't contain the error handling part. Furthermore, in case we need to support a custom set of chars with its own mapping mechanism, we may use the approach with GUID->Whatever char mapping. We can agree to use unicode or whatever. The idea is to specify a proper "language" which all of the actors in the system will understand. So, don't be surprised if you get a call from Mars soon :)

115-51-84-86-95-60-52-126-37-102-69-33-54-103-77-94