OPENQUERY and ADO.NET

microsoft.public.sqlserver.programming

27 Jul 2006 5:03 PM

Jay.Paolucci

I am having an issue with calling the following stored procedure from a .NET
application and need some help:

CREATE Procedure SelectMatchingCSUIDs
(
@SSNs varchar(8000)
)
AS
SET NOCOUNT ON

DECLARE @qry varchar(8000)

set @qry = 'SELECT *
FROM OPENQUERY(BANPROD, ''SELECT SUBSTR(CSUG_GP_XPID_V.PID, 2,
LENGTH(CSUG_GP_XPID_V.PID)) AS PID, CSUG_SPRIDEN_WEB_V.SPRIDEN_ID AS CSUID_ID
FROM CSUG_GP_XPID_V , CSUG_SPRIDEN_WEB_V WHERE CAST(CSUG_GP_XPID_V.PIDM AS
VARCHAR(15)) = CAST(CSUG_SPRIDEN_WEB_V.SPRIDEN_PIDM AS VARCHAR(15)) AND
(SUBSTR(CSUG_GP_XPID_V.PID, 2, LENGTH(CSUG_GP_XPID_V.PID) -1) IN ('+ @SSNs +
')) AND (LENGTH(TRIM(TRANSLATE(CSUG_SPRIDEN_WEB_V.SPRIDEN_ID, ''''
+-.0123456789'''','''' ''''))) IS NULL)'')'
exec(@qry)

SET NOCOUNT OFF
RETURN
GO

The problem is that the stored procedure runs fine when run on its own
through query analyzer, however when I call it from a .NET application I get
the following error:

{"Could not process object 'SELECT SUBSTR(CSUG_GP_XPID_V.PID, 2,
LENGTH(CSUG_GP_XPID_V.PID)) AS PID, CSUG_SPRIDEN_WEB_V.SPRIDEN_ID AS CSUID_ID
FROM CSUG_GP_XPID_V , CSUG_SPRIDEN_WEB_V WHERE CAST(CSUG_GP_XPID_V.PIDM AS
VARCHAR(15)) = CAST(CSUG_SPRIDEN_WEB_V.SPRIDEN_PIDM AS VARCHAR(15)) AND
(SUBSTR(CSUG_GP_XPID_V.PID, 2, LENGTH(CSUG_GP_XPID_V.PID) -1) IN
(''273806045'',''467970186'',''390607925'',..."}

The error number is the ever so gratifying 7357

Here is an Example of the code calling the procedure:

ArrayList paramList = new ArrayList();
paramList.Add(DbUtil.SafeSqlParam("@SSNs", SSNs));
SqlParameter[] param =
(SqlParameter[])paramList.ToArray(typeof(SqlParameter));
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(DbUtil.SafeSqlParam("@SSNs", SSNs));
cmd.CommandText ="SelectMatchingCSUIDs";
cmd.CommandTimeout = 240;
con.Open();
reader = cmd.ExecuteReader();

27 Jul 2006 5:12 PM

Steve Kass

It's darn near impossible to answer the question without seeing the
actual query being executed. I'm sure it doesn't end in
''390607925'',...". Using dynamic SQL this way is a bad idea for at
least two reasons, one of which you are seeing now. You have no clue
what queries are being executed, since part of the query text is
supplied by a user of the application. That leads to the second and
perhaps more serious problem, the risk of SQL injection attacks, if
someone submits a values of @SSNs that contains malicious code.

I suggest you read http://www.sommarskog.se/dynamic_sql.html and
http://www.sommarskog.se/arrays-in-sql.html, which should direct you
towards a better solution of this problem.

Steve Kass
Drew University
www.stevekass.com

Jay.Paolucci wrote:

Show quote

>I am having an issue with calling the following stored procedure from a .NET
>application and need some help:
>
>CREATE Procedure SelectMatchingCSUIDs
>(
> @SSNs varchar(8000)
>)
>AS
>SET NOCOUNT ON
>
>DECLARE @qry varchar(8000)
>
>set @qry = 'SELECT *
>FROM OPENQUERY(BANPROD, ''SELECT SUBSTR(CSUG_GP_XPID_V.PID, 2,
>LENGTH(CSUG_GP_XPID_V.PID)) AS PID, CSUG_SPRIDEN_WEB_V.SPRIDEN_ID AS CSUID_ID
>FROM CSUG_GP_XPID_V , CSUG_SPRIDEN_WEB_V WHERE CAST(CSUG_GP_XPID_V.PIDM AS
>VARCHAR(15)) = CAST(CSUG_SPRIDEN_WEB_V.SPRIDEN_PIDM AS VARCHAR(15)) AND
>(SUBSTR(CSUG_GP_XPID_V.PID, 2, LENGTH(CSUG_GP_XPID_V.PID) -1) IN ('+ @SSNs +
>')) AND (LENGTH(TRIM(TRANSLATE(CSUG_SPRIDEN_WEB_V.SPRIDEN_ID, ''''
>+-.0123456789'''','''' ''''))) IS NULL)'')'
>exec(@qry)
>
>SET NOCOUNT OFF
>RETURN
>GO
>
>The problem is that the stored procedure runs fine when run on its own
>through query analyzer, however when I call it from a .NET application I get
>the following error:
>
>{"Could not process object 'SELECT SUBSTR(CSUG_GP_XPID_V.PID, 2,
>LENGTH(CSUG_GP_XPID_V.PID)) AS PID, CSUG_SPRIDEN_WEB_V.SPRIDEN_ID AS CSUID_ID
>FROM CSUG_GP_XPID_V , CSUG_SPRIDEN_WEB_V WHERE CAST(CSUG_GP_XPID_V.PIDM AS
>VARCHAR(15)) = CAST(CSUG_SPRIDEN_WEB_V.SPRIDEN_PIDM AS VARCHAR(15)) AND
>(SUBSTR(CSUG_GP_XPID_V.PID, 2, LENGTH(CSUG_GP_XPID_V.PID) -1) IN
>(''273806045'',''467970186'',''390607925'',..."}
>
>The error number is the ever so gratifying 7357
>
>Here is an Example of the code calling the procedure:
>
>ArrayList paramList = new ArrayList();
>paramList.Add(DbUtil.SafeSqlParam("@SSNs", SSNs));
>SqlParameter[] param =
>(SqlParameter[])paramList.ToArray(typeof(SqlParameter));
>SqlCommand cmd = new SqlCommand();
>cmd.Connection = con;
>cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add(DbUtil.SafeSqlParam("@SSNs", SSNs));
>cmd.CommandText ="SelectMatchingCSUIDs";
>cmd.CommandTimeout = 240;
>con.Open();
>reader = cmd.ExecuteReader();
>
>

27 Jul 2006 6:07 PM

rculver

Actually, I would bet that your problem is exactly the same as mine (several
postings below entitled TRUSTWORTHY). Are you using SQL 2005 or 2000?

Show quote

"Jay.Paolucci" <Jay.Paolu***@discussions.microsoft.com> wrote in message
news:8CAD3E58-58BF-48F0-ACAA-83A87CA38CE1@microsoft.com...
>I am having an issue with calling the following stored procedure from a
>.NET
> application and need some help:
>
> CREATE Procedure SelectMatchingCSUIDs
> (
> @SSNs varchar(8000)
> )
> AS
> SET NOCOUNT ON
>
> DECLARE @qry varchar(8000)
>
> set @qry = 'SELECT *
> FROM OPENQUERY(BANPROD, ''SELECT SUBSTR(CSUG_GP_XPID_V.PID, 2,
> LENGTH(CSUG_GP_XPID_V.PID)) AS PID, CSUG_SPRIDEN_WEB_V.SPRIDEN_ID AS
> CSUID_ID
> FROM CSUG_GP_XPID_V , CSUG_SPRIDEN_WEB_V WHERE CAST(CSUG_GP_XPID_V.PIDM AS
> VARCHAR(15)) = CAST(CSUG_SPRIDEN_WEB_V.SPRIDEN_PIDM AS VARCHAR(15)) AND
> (SUBSTR(CSUG_GP_XPID_V.PID, 2, LENGTH(CSUG_GP_XPID_V.PID) -1) IN ('+ @SSNs
> +
> ')) AND (LENGTH(TRIM(TRANSLATE(CSUG_SPRIDEN_WEB_V.SPRIDEN_ID, ''''
> +-.0123456789'''','''' ''''))) IS NULL)'')'
> exec(@qry)
>
> SET NOCOUNT OFF
> RETURN
> GO
>
> The problem is that the stored procedure runs fine when run on its own
> through query analyzer, however when I call it from a .NET application I
> get
> the following error:
>
> {"Could not process object 'SELECT SUBSTR(CSUG_GP_XPID_V.PID, 2,
> LENGTH(CSUG_GP_XPID_V.PID)) AS PID, CSUG_SPRIDEN_WEB_V.SPRIDEN_ID AS
> CSUID_ID
> FROM CSUG_GP_XPID_V , CSUG_SPRIDEN_WEB_V WHERE CAST(CSUG_GP_XPID_V.PIDM AS
> VARCHAR(15)) = CAST(CSUG_SPRIDEN_WEB_V.SPRIDEN_PIDM AS VARCHAR(15)) AND
> (SUBSTR(CSUG_GP_XPID_V.PID, 2, LENGTH(CSUG_GP_XPID_V.PID) -1) IN
> (''273806045'',''467970186'',''390607925'',..."}
>
> The error number is the ever so gratifying 7357
>
> Here is an Example of the code calling the procedure:
>
> ArrayList paramList = new ArrayList();
> paramList.Add(DbUtil.SafeSqlParam("@SSNs", SSNs));
> SqlParameter[] param =
> (SqlParameter[])paramList.ToArray(typeof(SqlParameter));
> SqlCommand cmd = new SqlCommand();
> cmd.Connection = con;
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add(DbUtil.SafeSqlParam("@SSNs", SSNs));
> cmd.CommandText ="SelectMatchingCSUIDs";
> cmd.CommandTimeout = 240;
> con.Open();
> reader = cmd.ExecuteReader();