Escape Characters in Strings

[Gene Wirchenko]

Dear SQLers:

Does SQL Server have any string escape characters besides
doubling of quotation marks as in
'This is a single quotation mark('').'
"This is a double quotation mark("")."
I will have text which could contain both.

While I am at it, I want to handle any other special character
sequences.

I am referring to plain string values as would be stored in a
column, not LIKE strings, etc.

Why, yes, I am sanitising input. It is from a Web browser so I
do not see how I can avoid using sanitising. If there is such a
solution, please let me know.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
Does SQL Server have any string escape characters besides
doubling of quotation marks as in
'This is a single quotation mark('').'
"This is a double quotation mark("")."
I will have text which could contain both.

Note that " is not a string delimiter, it is an identifier delimiter.
...unless QUOTED_IDENTIFIER is off, in which case it is a string delimiter,
but that's a legacy setting you should stay away from.

And, no, there is no other choice but double the single quotes. Or double
the double quotes or right brackets in an identifier.

Post by Gene Wirchenko
Why, yes, I am sanitising input. It is from a Web browser so I
do not see how I can avoid using sanitising. If there is such a
solution, please let me know.

It's not clear to me why want to sanitise. You are not wrting code like:

sSQL = "SELECT ... FROM Orders WHERE CustomerID = " & custid_field

are you? As long as you pass all user input as parameters in a proper way,
there is no need to modify the user input from a strict SQL perspective.

[Gene Wirchenko]

On Wed, 22 Aug 2012 07:33:53 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
Does SQL Server have any string escape characters besides
doubling of quotation marks as in
'This is a single quotation mark('').'
"This is a double quotation mark("")."
I will have text which could contain both.

Note that " is not a string delimiter, it is an identifier delimiter.
...unless QUOTED_IDENTIFIER is off, in which case it is a string delimiter,
but that's a legacy setting you should stay away from.
And, no, there is no other choice but double the single quotes. Or double
the double quotes or right brackets in an identifier.

My question was really whether there are any other escape
characters? Are there?

Post by Erland Sommarskog

Post by Gene Wirchenko
Why, yes, I am sanitising input. It is from a Web browser so I
do not see how I can avoid using sanitising. If there is such a
solution, please let me know.

sSQL = "SELECT ... FROM Orders WHERE CustomerID = " & custid_field
are you? As long as you pass all user input as parameters in a proper way,
there is no need to modify the user input from a strict SQL perspective.

No. I will be passing parameters, but I need to be sure that
they are properly delimited and escaped. For example, if I do not
escape quotes, it may allow trouble.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
My question was really whether there are any other escape
characters? Are there?

No.

Post by Gene Wirchenko
No. I will be passing parameters, but I need to be sure that
they are properly delimited and escaped. For example, if I do not
escape quotes, it may allow trouble.

As long as you don't build SQL strings from input data, there is no trouble.
No need to delimit, no need to escape. Again from a strict SQL perspective.
There may be business rules requiring you to deal with certain characters.
But given that the apostrophe is an essential character in English
ortography, I don't think that the single quote is one these characters.

[andyza]

On Wed, 22 Aug 2012 07:33:53 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog
sSQL = "SELECT ... FROM Orders WHERE CustomerID = " & custid_field
are you? As long as you pass all user input as parameters in a proper way,
there is no need to modify the user input from a strict SQL perspective.

How would you write that SELECT so that parameters are passed in a
proper way?

[Bob Barrows]

Post by Gene Wirchenko
On Wed, 22 Aug 2012 07:33:53 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog
sSQL = "SELECT ... FROM Orders WHERE CustomerID = " & custid_field
are you? As long as you pass all user input as parameters in a
proper way, there is no need to modify the user input from a strict
SQL perspective.

How would you write that SELECT so that parameters are passed in a
proper way?

Like this:
sSQL = "SELECT ... FROM Orders WHERE CustomerID = ?"

The method of safely passing a parameter value to that parameter token
depends on the programming language and data access technology you are
using.
With vb/vbscript, you would use an ADODB Command object, instantiating a
Parameter object and setting its Value property to the value for which you
wish to search, in the above case, the value of that custid_field variable.
With a .Net language, you would use an ADO.Net xxxCommand object in a
similar way.

Doing it this way relieves the developer of the necessity of worrying about
things like literal quotes or apostrophes in the data, as well as preventing
sql injection..

[Erland Sommarskog]

Post by Bob Barrows

Post by Gene Wirchenko
On Wed, 22 Aug 2012 07:33:53 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog
sSQL = "SELECT ... FROM Orders WHERE CustomerID = " & custid_field
are you? As long as you pass all user input as parameters in a
proper way, there is no need to modify the user input from a strict
SQL perspective.

How would you write that SELECT so that parameters are passed in a
proper way?

sSQL = "SELECT ... FROM Orders WHERE CustomerID = ?"

For API that uses OLE DB or ODBC. For ADO .Net (which is what you should
use), you say:

sSQL = "SELECT ... FROM Orders WHERE CustomerID = @custid"

And then you need to define the parameters accordingly.

[andyza]

On Sat, 01 Sep 2012 22:52:25 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Bob Barrows
sSQL = "SELECT ... FROM Orders WHERE CustomerID = ?"

For API that uses OLE DB or ODBC. For ADO .Net (which is what you should
And then you need to define the parameters accordingly.

Thanks.

How would you do it on a non-ms platform like PHP?

[Bob Barrows]

Post by andyza
On Sat, 01 Sep 2012 22:52:25 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Bob Barrows
sSQL = "SELECT ... FROM Orders WHERE CustomerID = ?"

For API that uses OLE DB or ODBC. For ADO .Net (which is what you
And then you need to define the parameters accordingly.

Thanks.
How would you do it on a non-ms platform like PHP?

Ask in a PHP newgroup?

[andyza]

On Sun, 2 Sep 2012 07:58:28 -0400, "Bob Barrows"

Post by Bob Barrows

Post by andyza
How would you do it on a non-ms platform like PHP?

Ask in a PHP newgroup?

They'll just tell you to switch to MySQL...

[Bob Barrows]

Post by andyza
On Sun, 2 Sep 2012 07:58:28 -0400, "Bob Barrows"

How would you do it on a non-ms platform like PHP? Ask in a PHP
newgroup?

They'll just tell you to switch to MySQL...

Why? PHP has nothing capable of communicating with sql server?

[andyza]

On Mon, 3 Sep 2012 16:04:17 -0400, "Bob Barrows"

Post by Bob Barrows

Post by andyza
They'll just tell you to switch to MySQL...

Why? PHP has nothing capable of communicating with sql server?

PHP can communicate with MSSQL - in a limited sort of way. But
Microsoft has a SQL Server Driver for PHP which works well for most
PHP applications. And there's also the FreeTDS project...

But most PHP development happens on MySQL, so many PHP/MSSQL questions
on forums get responded to with stupid "just switch to MySQL"
comments.

[Bob Barrows]

Post by andyza
On Mon, 3 Sep 2012 16:04:17 -0400, "Bob Barrows"

Post by Bob Barrows

Post by andyza
They'll just tell you to switch to MySQL...

Why? PHP has nothing capable of communicating with sql server?

PHP can communicate with MSSQL - in a limited sort of way. But
Microsoft has a SQL Server Driver for PHP which works well for most
PHP applications. And there's also the FreeTDS project...
But most PHP development happens on MySQL, so many PHP/MSSQL questions
on forums get responded to with stupid "just switch to MySQL"
comments.

So tell them you're usiong mysql - the same answer will likely apply to sql

[andyza]

On Thu, 6 Sep 2012 18:35:42 -0400, "Bob Barrows"

Post by Bob Barrows
So tell them you're usiong mysql - the same answer will likely apply to sql

:->
I'll tell them I'm using MS Access for a multi-user enteprise
solution!

[Bob Barrows]

Post by andyza
On Thu, 6 Sep 2012 18:35:42 -0400, "Bob Barrows"

Post by Bob Barrows
So tell them you're usiong mysql - the same answer will likely apply to sql

:->
I'll tell them I'm using MS Access for a multi-user enteprise
solution!

Again, the answer will likely apply to sql server as well. Passing parameter
values is passing parameter values, regardless of the backend database.

[Erland Sommarskog]

Post by andyza
How would you do it on a non-ms platform like PHP?

Since the Microsoft PHP driver is based on ODBC, I guess you should use
? as the parameter holder. Then exactly how you would specify the
parameters, I don't know since I don't know PHP. But at a quick look at
http://msdn.microsoft.com/en-us/library/cc296184%28v=sql.105%29
indicates that you can pass parameter values with sqlsrv_query.