I encoutered a strange behavior using the exec command and I could repreduce
the behavior with the print command:
the command:
print '1
2
3'
is doing it's jub, but if i add go inside the string I get the floowing
error:
print '1
2
go
3'
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '1
2
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1
2
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '3'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
windows 2003 server with service pack 1
this problem also occurs is the string is sent as a parameter to a stored
procedure using the exec command.
please helpMartin,
This is not a bug. Go is a command that tells SQL Server that this is the
end of a batch of T-SQL statements. If you execute some code in Query
Analyzer and one line has the go word alone, SQL Server will take this as th
e
Go command. For example, try this
print '1
2
go 3
4'
Anyway, I would avoid the go word at the beginning of a line, if possible.
Ben Nevarez, MCDBA, OCP
Database Administrator
"martin" wrote:
> I encoutered a strange behavior using the exec command and I could repredu
ce
> the behavior with the print command:
> the command:
> print '1
> 2
> 3'
> is doing it's jub, but if i add go inside the string I get the floowing
> error:
> print '1
> 2
> go
> 3'
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '3'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
>
> can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
> windows 2003 server with service pack 1
> this problem also occurs is the string is sent as a parameter to a stored
> procedure using the exec command.
> please help
>
>|||well, ben,
of course it's a bug
if I get a string as input from a user of a web application, and encode the
string as required like replacing a single apostrophe ' with 2 '' in order
to not break the SQL syntax the same goes with the GO keyword or other
keyword like SELECT.
what exactly should I do in order to pass this kind of a parameter to a
stored procedure? encode it with some way to it's numric ascii
representation?
just image that I will ask you to avoid the END keyword in the beggining of
a sentence in your reply to me, would that not be considered as a bug?
not all string passes as a parameter to a stored procedure are in my control
at all. most of them are not.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:968CB5C7-A808-4767-BAAC-C2F59114E390@.microsoft.com...
> Martin,
> This is not a bug. Go is a command that tells SQL Server that this is the
> end of a batch of T-SQL statements. If you execute some code in Query
> Analyzer and one line has the go word alone, SQL Server will take this as
> the
> Go command. For example, try this
> print '1
> 2
> go 3
> 4'
> Anyway, I would avoid the go word at the beginning of a line, if possible.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "martin" wrote:
>|||martin wrote:
> well, ben,
> of course it's a bug
> if I get a string as input from a user of a web application, and encode th
e
> string as required like replacing a single apostrophe ' with 2 '' in order
> to not break the SQL syntax the same goes with the GO keyword or other
> keyword like SELECT.
>
If you accept string input in that manner and use it for dynamic SQL
then your web application is buggy, dangerous and insecure. This
problem is called SQL Injection and is one important reason why you
should never create dynamic strings out of unverified,
non-parameterized user input. The proper and safe way to do it is to
use parameters in your client code (the ADO parameters collection if
you are using ADO for example).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||martin
> what exactly should I do in order to pass this kind of a parameter to a
> stored procedure? encode it with some way to it's numric ascii
> representation?
It is not a bug, please post exactly what are you doing in order to help
you?
"martin" <news.microsoft.com> wrote in message
news:eAcI3PCbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> well, ben,
> of course it's a bug
> if I get a string as input from a user of a web application, and encode
> the string as required like replacing a single apostrophe ' with 2 '' in
> order to not break the SQL syntax the same goes with the GO keyword or
> other keyword like SELECT.
> what exactly should I do in order to pass this kind of a parameter to a
> stored procedure? encode it with some way to it's numric ascii
> representation?
> just image that I will ask you to avoid the END keyword in the beggining
> of a sentence in your reply to me, would that not be considered as a bug?
> not all string passes as a parameter to a stored procedure are in my
> control at all. most of them are not.
>
>
>
>
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:968CB5C7-A808-4767-BAAC-C2F59114E390@.microsoft.com...
>|||well,
so please explain that:
my app does uses ADO.NET and ado.net succeedes to execute SQL statements
that fails to execute in query analyzer.
is there some magic here?
after executing stored procedure with command object, adding a string
parameter
in the profiler I see the following SQL statement executed from ADO.NET:
declare @.P1 int
set @.P1=33
exec sp_insert_string '748F4655-9106-4D45-A0A2-1AA95C4C8912', 2, N'test',
N'--test
go
-- test
', N'asd', N'James', NULL, @.P1 output
select @.P1
the same stored procedure fails to execute from query analyzer.
I thought the answer will be in some changes to the default behavior of
ADO.NET so I also executed the line performed by ADO.NET to set default
execution variables, with no help:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146383014.820122.76100@.j33g2000cwa.googlegroups.com...
> martin wrote:
> If you accept string input in that manner and use it for dynamic SQL
> then your web application is buggy, dangerous and insecure. This
> problem is called SQL Injection and is one important reason why you
> should never create dynamic strings out of unverified,
> non-parameterized user input. The proper and safe way to do it is to
> use parameters in your client code (the ADO parameters collection if
> you are using ADO for example).
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I've just checked this out on SSMS the SQL 2005 replacement for Query
Analyser.
The problem has been fixed as the print command works perfectly.
The are problems with the Query Analyser SQL interpreter. In a nutshell,
avoid placing Go on it's own at the begginning of the line.
You could change your calling code to do something like
Print '1
2
' + 'Go
3
4'
It's a bodge I know, but it should work ok.
To "Fix" the problem you should upgrade to SQL2005, as I doubt that there
will be another SP for SQL2000.
As mentioned by one of the other posters, you need to make sure that you are
not suseptable to a SQL Injection attack.
OK, you might say that all user input must go through your front end, and
that's 100% secure. But consider the what if scenario.
The simplest way to avoid injection attacks is to use Parameterized queries,
or stored procedures.
The important thing is that any Dynamic SQL uses the sp_executesql command
and uses the parameters properly. i.e. Do not build your query like it's
adhoc sql.
Colin.
"martin" <news.microsoft.com> wrote in message
news:%23GW81yBbGHA.3916@.TK2MSFTNGP03.phx.gbl...
>I encoutered a strange behavior using the exec command and I could
>repreduce the behavior with the print command:
> the command:
> print '1
> 2
> 3'
> is doing it's jub, but if i add go inside the string I get the floowing
> error:
> print '1
> 2
> go
> 3'
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '3'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
>
> can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
> windows 2003 server with service pack 1
> this problem also occurs is the string is sent as a parameter to a stored
> procedure using the exec command.
> please help
>|||martin wrote:
> well,
> so please explain that:
> my app does uses ADO.NET and ado.net succeedes to execute SQL statements
> that fails to execute in query analyzer.
> is there some magic here?
> after executing stored procedure with command object, adding a string
> parameter
> in the profiler I see the following SQL statement executed from ADO.NET:
> declare @.P1 int
> set @.P1=33
> exec sp_insert_string '748F4655-9106-4D45-A0A2-1AA95C4C8912', 2, N'test',
> N'--test
> go
> -- test
> ', N'asd', N'James', NULL, @.P1 output
> select @.P1
> the same stored procedure fails to execute from query analyzer.
>
GO is not a T-SQL statement. It is a batch separator used by Query
Analyzer and the other client utilities so this behaviour is correct.
BTW you sould not use the sp_ prefix for user procs. sp_ is reserved
for system procs and may adversely affect performance and reliability
if used in databases other than Master.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ben Nevarez (BenNevarez@.discussions.microsoft.com) writes:
> This is not a bug. Go is a command that tells SQL Server that this is the
> end of a batch of T-SQL statements.
No. GO is just an identifier as far as SQL Server is concerned. That is,
it is not a command or anything.
However, it is a command that is used by many client-tools to signify
the end of batch, that's true.
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
