ERROR: Subreport: escaping

Showing posts with label escaping. Show all posts

Monday, March 26, 2012

escaping single quotes

Hi,

I need to have an varchar value with single quotes. For eg: the below code throws compilation error.

Declare @.val VARCHAR(20)
SELECT @.val = ''+name+''
print @.val

Error: Invalid column 'name'

I want to print name enclosed with single quotes. Please guide me.

Regards,
SamDeclare @.val VARCHAR(20)
SELECT @.val = 'Kaiowas'
PRINT @.VAL

EDIT (added): SET @.VAL = @.VAL + '''Kaiowas'''
EDIT (added): PRINT @.VAL

SET @.VAL = @.VAL + '''' + @.VAL + ''''
PRINT @.VAL

Escaping international (unicode) characters in string

Y'all:

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:

String str = "This is a\u1245 test.";

in Oracle's SQL dialect, it appears that I can accomplish the same thing:

INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a\1245 test.");

I've googled and researched through the MSDN, and haven't discovered a
similar construct in SQL Server. I am already aware of the UNISTR()
function, and the NCHAR() function, but those aren't going to work well if
there are more than a few international characters embedded within a
string.

Does anyone have a better suggestion?

Thanks muchly!
GRB

--
---------------------
Greg R. Broderick usenet200705@.blackholio.dyndns.org
A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:
>
String str = "This is a\u1245 test.";

SELECT @.str = 'This is a' + nchar(1245) + ' test'

Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:

SELECT @.str = 'This is a' + nchar(0x1245) + ' test'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.sewrote in
news:Xns993F9F38ABD05Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

>I am needing some way, in the SQL Server dialect of SQL, to escape
>unicode code points that are embedded within an nvarchar string in a
>SQL script, e.g. in Java I can do:
>>
>String str = "This is a\u1245 test.";

>
SELECT @.str = 'This is a' + nchar(1245) + ' test'
>
Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:
>
SELECT @.str = 'This is a' + nchar(0x1245) + ' test'

When there are more than one or two non-US-ASCII characters in the string,
this quickly becomes impractically unwieldy, thus my comment in my original
posting:

-- quote --

I am already aware of the UNISTR() function, and the NCHAR() function, but
those aren't going to work well if there are more than a few international
characters embedded within a string.

-- quote --

Thanks anyway, though. :-)

--
---------------------
Greg R. Broderick usenet200705@.blackholio.dyndns.org
A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------|||Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

When there are more than one or two non-US-ASCII characters in the
string, this quickly becomes impractically unwieldy, thus my comment in
my original posting:

If the are in sequence, you could do:

convert(nvarchar, 0x34123512...)

although this is certainly not too funny as you have twitch the bytes
around.

Another solution to use something like Microsoft Visual Keyboard, and
simply put the actual characters there.

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();
On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStrXing].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).
|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegro ups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnection
String].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=[/vb
col]
tr=ADing].ToString());[vbcol=seagreen]
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnecti
onString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

try
{
int result = command.ExecuteNonQuery();

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

connection.Close();If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"eggie5" <eggie5@.gmail.comwrote in message
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

>I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
>
I'm not using any fancy ado.net objects:
>
string sql= [whatever the user passes in]
>
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
>
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
>
>
try
{
int result = command.ExecuteNonQuery();
>
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
>
connection.Close();
>

|||oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
>
This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"eggie5" <egg...@.gmail.comwrote in message
>
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...
>

Quote:

Originally Posted by

Quote:

Originally Posted by

I'm not using any fancy ado.net objects:

Quote:

Originally Posted by

string sql= [whatever the user passes in]

Quote:

Originally Posted by

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStr ing].ToString());
connection.Open();

Quote:

Originally Posted by

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

Quote:

Originally Posted by

try
{
int result = command.ExecuteNonQuery();

Quote:

Originally Posted by

Quote:

Originally Posted by

connection.Close();

escaping an apostrophe in MSSQL

Hi, i'm having problems executing the following

SET @.SQLAH = 'SELECT sub_id WHERE '
SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = Hotel'
EXEC(@.SQLAH)

Its getting stuck at Hotel. I realise that it should include an apostrophe either side like so:

..
SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = 'Hotel' '
..

But this escapes the string, how would i escape an apostrophe in a string?

I thought maybe:

SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = \'Hotel\' '

But no joy :confused:

ThanksAs far as I know MS SQL is using the ANSI standard for that: two single quotes:

SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = 'Hotel'''

(Don't know if it works inside a procedure though)|||hi

nope, it doesnt work - i'm using a stored procedure
:eek:|||SET @.SQLAH = @.SQLAH + 'VENUE_TYPE = ''Hotel'' '-PatP|||thats the badger! cheerssql

escaping a transaction

The code is
proc1
begin tgan
insert
exec proc2
commit tran
end
proc2
begin tran
commit tran
exec proc3 -- do not want to it be in the proc1's tran
end
The desire is to escape the execution of proc3 from the transaction started
by proc1.
If you like, the apology for the decision is: The proc3 does some complex
things (a couple of records are inserted, deleted, updated). This must be
done 1) atomically (all or nothing) but the operations are 2) expensive and
3) must always commit. So, for performance reasons, I have decieded to do it
transceding the transaction. In case of failure, which may happen only
because of system shut down, the state can be restored manually.I'm not sure I completely understand your question, however you may want to
take a look at the following stuff.
Check out ROLLBACK TRAN
http://msdn2.microsoft.com/en-us/library/ms181299.aspx
and TRY ... CATCH (SQL 2005 only)
http://msdn2.microsoft.com/en-us/library/ms175976.aspx
--
Ekrem Önsoy
"valentin tihomirov" <V_tihomirov@.best.ee> wrote in message
news:eNi74gCFIHA.3400@.TK2MSFTNGP03.phx.gbl...
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction
> started by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive
> and 3) must always commit. So, for performance reasons, I have decieded to
> do it transceding the transaction. In case of failure, which may happen
> only because of system shut down, the state can be restored manually.
>|||Hi
I am not sure why you would want to do this as it would prolong the length
of the transaction without participating in it!
You may want to look at service broker, you could call xp_cmdshell to run a
query.
John
"valentin tihomirov" wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
>
>|||On 21 Oct, 22:21, "valentin tihomirov" <V_tihomi...@.best.ee> wrote:
> The code is
> proc1
> begin tgan
> insert
> exec proc2
> commit tran
> end
> proc2
> begin tran
> commit tran
> exec proc3 -- do not want to it be in the proc1's tran
> end
> The desire is to escape the execution of proc3 from the transaction started
> by proc1.
> If you like, the apology for the decision is: The proc3 does some complex
> things (a couple of records are inserted, deleted, updated). This must be
> done 1) atomically (all or nothing) but the operations are 2) expensive and
> 3) must always commit. So, for performance reasons, I have decieded to do it
> transceding the transaction. In case of failure, which may happen only
> because of system shut down, the state can be restored manually.
proc1
begin tran
insert
commit tran
exec proc2
end
--
David Portas

escaping a string

hi guys

please help me. i've never user stored procs before but here is my problem.

this is only what i am allowed to display but it shows my problem

declare @.suite varchar(10),@.company varchar(1)
set @.suite = 'brutus'

set @.company = 'A'
exec ('insert into tot (SuiteName,Company) values ('+@.suite+','+@.company+')')

when i exec the query it says that "brutus" is an invalid column name. i know that i need to insert a extra ' but i don't know how or what is the escape character. please help me.

Hi,

this should be accomplishedby this one here:

exec ('insert into tot (SuiteName,Company) values ('''+@.suite+''','''+@.company+''')')

But I would rather prefer not using dynamic sql in this case:

insert into tot (SuiteName,Company) values (@.suite,@.company)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

thanks that helps but when i do this

1. exec ('alter table tot add '+@.UnitT+' varchar(3) DEFAULT '' WITH VALUES')

2. alter table tot add @.UnitT varchar(3) DEFAULT ' ' WITH VALUES

not one work. in 1 i get the same problem and in 2 it tells met there is a problem with syntax before @.unit

|||

No, therefore you would need dynamic SQL, that will not function with the second opion I posted.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK THANKS

i used your first solution with the ''' multiple quotes and it works perfect. thanks

Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a nor
mal string value. When I remove the comma everything works fine. Is there a
way to escape the comma? I've spent a great deal of time looking for the ans
wer, apparently I am not lo
oking in the right places. Any help would be appreciated.Can you be a bit more specific, or show us a repro? Below statement work jus
t like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal st
ring value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've sp
ent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help wo
uld be appreciated.|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect
syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work j
ust like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've
spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help
would be appreciated.
>
>|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrec
t syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm sorry but that is not something I can execute as you didn't post the CRE
ATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the stri
ng definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorre
ct syntax near ','.
>
> "Tibor Karaszi" wrote:
>
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Escaping a comma in a LIKE statement

A simple question I'm sure...
I am having problems with the following statement:
Select * from data where
Company LIKE '%HEI,%'
It appears that the comma is being interpreted as something other than a normal string value. When I remove the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking for the answer, apparently I am not lo
oking in the right places. Any help would be appreciated.
Can you be a bit more specific, or show us a repro? Below statement work just like expected, i.e., the comma
does not get any special treatment:
SELECT * FROM
(
SELECT 'HEL,oo' AS x
UNION
SELECT 'HELOO' AS x
) AS d
WHERE x LIKE 'HEL,%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> A simple question I'm sure...
> I am having problems with the following statement:
> Select * from data where
> Company LIKE '%HEI,%'
> It appears that the comma is being interpreted as something other than a normal string value. When I remove
the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking
for the answer, apparently I am not looking in the right places. Any help would be appreciated.
|||Sure. Here's the full query and the resulting error message:
Query:
Select * From data
WHERE Company like '%HEI %',
And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Here's the error message:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
"Tibor Karaszi" wrote:

> Can you be a bit more specific, or show us a repro? Below statement work just like expected, i.e., the comma
> does not get any special treatment:
> SELECT * FROM
> (
> SELECT 'HEL,oo' AS x
> UNION
> SELECT 'HELOO' AS x
> ) AS d
> WHERE x LIKE 'HEL,%'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
> news:CE4ECBB0-2DEB-479E-8277-736B3ECA2138@.microsoft.com...
> the comma everything works fine. Is there a way to escape the comma? I've spent a great deal of time looking
> for the answer, apparently I am not looking in the right places. Any help would be appreciated.
>
>
|||I see the problem. Don't know how I missed the comma. Thanks anyway
"MSchlaud" wrote:
[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
> "Tibor Karaszi" wrote:
|||On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:

>Sure. Here's the full query and the resulting error message:
>Query:
>Select * From data
>WHERE Company like '%HEI %',
>And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
>Here's the error message:
>ODBC Error Code = 37000 (Syntax error or access violation)
>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
Hi MSchlaud,
You have the comma outside of the apostrophe.
In your original question, you said Company LIKE '%HEI,%' (with the comma
as part of the LIKE argument).
In the above, you have Company LIKE '%HEI% ', (with a space between the
apostrophes and a comma after the closing apoostrophe)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I'm sorry but that is not something I can execute as you didn't post the CREATE TABLE and INSERT statements.
I.e., I cannot try to reproduce that error message.
However, trying to parse only give me that you have a comma *after* the string definition (outside the
string), before the word AND. Try below:
SELECT * FROM data
WHERE Company LIKE '%HEI %'
AND RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"MSchlaud" <MSchlaud@.discussions.microsoft.com> wrote in message
news:B2396540-2022-4711-A1F2-331F46BD1DC2@.microsoft.com...[vbcol=seagreen]
> Sure. Here's the full query and the resulting error message:
> Query:
> Select * From data
> WHERE Company like '%HEI %',
> And RequestDate BETWEEN {d '2004-06-01'} AND {d '2004-06-15'}
> Here's the error message:
> ODBC Error Code = 37000 (Syntax error or access violation)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.
>
> "Tibor Karaszi" wrote:
comma[vbcol=seagreen]
remove[vbcol=seagreen]
looking[vbcol=seagreen]
|||Yes, I see that now. Thanks for the quick responses.
"Hugo Kornelis" wrote:

> On Wed, 16 Jun 2004 13:31:01 -0700, MSchlaud wrote:
>
> Hi MSchlaud,
> You have the comma outside of the apostrophe.
> In your original question, you said Company LIKE '%HEI,%' (with the comma
> as part of the LIKE argument).
> In the above, you have Company LIKE '%HEI% ', (with a space between the
> apostrophes and a comma after the closing apoostrophe)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Escaping [characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
AlexThe REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Pe...br />
8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>sql

Escaping [ characters with text lengths over 4000 characters

In a previous post I mentioned you will get into problems when trying to
update DataSets containing rows with [ characters in ntext or text field
columns. Andrew Conrad (thanks) mentioned that you should escape these, like
so [[]. This will help you, except when the length of the value is over 4000
characters (for ntext) in length. After that the problem of zero affected
rows arises again, despite escaping. How come? How to solve?
Thanks.
Alex
The REPLACE fuction (and other SQL Server string functions) will not operate
on data larger than 8000 bytes -- for an NVARCHAR datatype, that means 4000
characters (2 bytes per character). Can you perform the replace on the
client, before passing the data to SQL Server?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Alex Thissen" <athissen A T killer-apps.nl> wrote in message
news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> In a previous post I mentioned you will get into problems when trying to
> update DataSets containing rows with [ characters in ntext or text field
> columns. Andrew Conrad (thanks) mentioned that you should escape these,
like
> so [[]. This will help you, except when the length of the value is over
4000
> characters (for ntext) in length. After that the problem of zero affected
> rows arises again, despite escaping. How come? How to solve?
> Thanks.
> Alex
>
|||Hi Adam,
Thanks for thinking with me on this one. I don't use the REPLACE functions.
Instead, I make sure that the OriginalVersion of my DataRow in the DataSet
has the replaced value. Then the SqlXmlAdapter builds the UPDATE statement
for me, but it uses an optimistic locking scheme by comparing all columns
with the original values.For the (n)text fields the LIKE operator is used,
but as I mentioned this one breaks with values over 8000 bytes and [ chars
in it. I also wrote a (teasing) weblog entry on it, that you can find here:
http://www.alexthissen.nl/weblog/Per...-8feda8c42070.
You might want to read up on it, since this problem is hardly related to
SQLXML. It could be circumvented if there was a possibility to influence the
SQL that is generated. An option to exclude fields from the optimistic
locking would solve it directly.
So, my question remains, given that the LIKE operator breaks for lengths
over 8000 bytes WITH escaped [ characters in it ( as [[] ) in it, how do I
get my DataSets that contain such values to get updated through SQLXML?
Thanks, Alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvUE$$VFFHA.1348@.TK2MSFTNGP14.phx.gbl...
> The REPLACE fuction (and other SQL Server string functions) will not
> operate
> on data larger than 8000 bytes -- for an NVARCHAR datatype, that means
> 4000
> characters (2 bytes per character). Can you perform the replace on the
> client, before passing the data to SQL Server?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Alex Thissen" <athissen A T killer-apps.nl> wrote in message
> news:uNIrcZPFFHA.3284@.TK2MSFTNGP09.phx.gbl...
> like
> 4000
>

Escape the '\' character

I noticed that the following character needs escaping with another \ but
only if it is not followed by another character
What is the rule
Are there any other character with that need escaping except ' (single
quote) and wildcard characters when used with LIKE
Thank you,
SamuelA lesser known fact about the Transact-SQL parser is that a backslash ('\')
is a continuation character (like the C programming language). When a
backslash is found at the end of a line in a literal string, the backslash
and line terminator characters are ignored. Specifying the additional
backslash isn't technically an escape, it's just another character in the
literal string. For example
SELECT 'test\
ing'
-- result is 'testing'
SELECT 'test\\
ing'
-- result is 'test\ing'
BTW, I first learned of this issue when helping a user who was obfuscating
data. The backslash and newline characters were getting dropped when the
algorithm introduced a backslash at the end of a line. This is yet one
more reason that one should always use parameteritized SQL statements.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>I noticed that the following character needs escaping with another \ but
>only if it is not followed by another character
> What is the rule
> Are there any other character with that need escaping except ' (single
> quote) and wildcard characters when used with LIKE
> Thank you,
> Samuel
>|||very interesting indeed,
thank you
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('\')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>|||So you never need to escape a letter except the single quote and except
wildcard character when using LIKE
What I still don't understand why if I type "A\" & VBCR & "B" I get
A
B
And if I type ' \' as the last character in the line in a multi line Textbox
it will NOT ignore it and I will get
A\
B
Why is that?
Thanks,
Samuel Shulman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('\')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>|||> And if I type ' \' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
I would expect that behavior if you using a parameterized SQL Statement.
However, if the SQL statement string is constructed like the example below,
you should get 'AB':
strSql = "INSERT INTO MyTable VALUES('" & _
Request("textBoxValue") & _
"')")
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> So you never need to escape a letter except the single quote and except
> wildcard character when using LIKE
> What I still don't understand why if I type "A\" & VBCR & "B" I get
> A
> B
> And if I type ' \' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
> Why is that?
> Thanks,
> Samuel Shulman
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language). When
>>a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>|||Can you please define what parameterized statement
Does is matter how the variable is assigned the value?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
> I would expect that behavior if you using a parameterized SQL Statement.
> However, if the SQL statement string is constructed like the example
> below, you should get 'AB':
> strSql = "INSERT INTO MyTable VALUES('" & _
> Request("textBoxValue") & _
> "')")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>> So you never need to escape a letter except the single quote and except
>> wildcard character when using LIKE
>> What I still don't understand why if I type "A\" & VBCR & "B" I get
>> A
>> B
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> Why is that?
>> Thanks,
>> Samuel Shulman
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language).
>>When a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \ but
>>only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>>
>|||> Can you please define what parameterized statement
A parameterized statement contains parameter markers instead of literal
values. Actual parameter values are substituted for parameter markers at
query execution time. Parameterized statements have several advantages,
such as improved security, no need for special quote handling and execution
plan reuse.
Below is an ADO example. ADO.NET has a slightly different object model but
the basic principle is the same and you can use named parameter markers with
the ADO.NET SqlClient provider.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "INSERT INTO MyTable VALUES(?)"
Set textBoxParameter = command.CreateParameter( _
"@.textBoxParameter", adVarchar, adParamInput, 50,
Request("textBoxValue"))
command.Parameters.Append textBoxParameter
Set Rs = command.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OEqmw%23MgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Can you please define what parameterized statement
> Does is matter how the variable is assigned the value?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> I would expect that behavior if you using a parameterized SQL Statement.
>> However, if the SQL statement string is constructed like the example
>> below, you should get 'AB':
>> strSql = "INSERT INTO MyTable VALUES('" & _
>> Request("textBoxValue") & _
>> "')")
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>> So you never need to escape a letter except the single quote and except
>> wildcard character when using LIKE
>> What I still don't understand why if I type "A\" & VBCR & "B" I get
>> A
>> B
>> And if I type ' \' as the last character in the line in a multi line
>> Textbox it will NOT ignore it and I will get
>> A\
>> B
>> Why is that?
>> Thanks,
>> Samuel Shulman
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>>A lesser known fact about the Transact-SQL parser is that a backslash
>>('\') is a continuation character (like the C programming language).
>>When a backslash is found at the end of a line in a literal string, the
>>backslash and line terminator characters are ignored. Specifying the
>>additional backslash isn't technically an escape, it's just another
>>character in the literal string. For example
>> SELECT 'test\
>> ing'
>> -- result is 'testing'
>> SELECT 'test\\
>> ing'
>> -- result is 'test\ing'
>> BTW, I first learned of this issue when helping a user who was
>> obfuscating data. The backslash and newline characters were getting
>> dropped when the algorithm introduced a backslash at the end of a line.
>> This is yet one more reason that one should always use parameteritized
>> SQL statements.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
>> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>>I noticed that the following character needs escaping with another \
>>but only if it is not followed by another character
>> What is the rule
>> Are there any other character with that need escaping except ' (single
>> quote) and wildcard characters when used with LIKE
>> Thank you,
>> Samuel
>>
>>
>>
>

Monday, March 26, 2012

escaping single quotes

Escaping international (unicode) characters in string

escaping data for update query

escaping data for update query

escaping data for update query

escaping data for update query

escaping an apostrophe in MSSQL

escaping a transaction

escaping a string

Escaping a comma in a LIKE statement

Escaping a comma in a LIKE statement

Escaping [characters with text lengths over 4000 characters

Escaping [ characters with text lengths over 4000 characters

Escape the '\' character

Escape the '\' character

ERROR: Subreport

Blog Archive

About Me