Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT

Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT

Problem with Dynamic Where Clause

I have a stored proc that accepts a varchar as a parameter. What is being
passed in is one or more IDs in a comma separated list. (ie '123,567,789')
In the where clause I want to pull are records where a value is IN the list
being passed in. (ie. WHERE column IN (@.Var))
When I run this stored proc I get an error because its treating
'123,567,789' as one varchar value insstead of 3 int values.
Is there anything I can do to work around this?
Thanks in advance,
Mike RFaking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>|||Mike wrote:
> I have a stored proc that accepts a varchar as a parameter. What is
> being passed in is one or more IDs in a comma separated list. (ie
> '123,567,789') In the where clause I want to pull are records where a
> value is IN the list being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
You have to use dynamic SQL to do what you want and you'll have to grant
users select access on the tables in question if they don't already have
those rights. That might be asecurity risk in your environment.
declare @.n nvarchar(1000)
Set @.n = N'Select col1 from mytable where col2 in (' + @.Var + N')'
Exec sp_executesql @.n
The other option is to create a temp table with all those IDs and join
the temp table with the main table in the query.
David Gugick
Imceda Software
www.imceda.com|||In article <Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl>,
mraeNOSPAM@.NOSPAMATALLcalibrus.com says...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>
http://www.sommarskog.se/arrays-in-...ist-of-integers
****************************************
************************
Tapio Kulmala
"Those are my principles. If you don't like them I have others."
- Groucho Marx
****************************************
************************|||http://www.aspfaq.com/2248
http://www.aspfaq.com/
(Reverse address to reply.)
"Mike" <mraeNOSPAM@.NOSPAMATALLcalibrus.com> wrote in message
news:Op3sqtnJFHA.220@.TK2MSFTNGP10.phx.gbl...
> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the
list
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>|||If you use the attached User Defined Function to convert the Delimited list
in =to a table variable, you can simply join your main query to this table
variable...
Here's the UDF
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
And in your stoored Proc, just join to the output of this udf as though it
was a table, containing a varchar() named sVal...
Select <stuff>
From Table T
Join dbo.ParseString(@.Var, ',') as V
On T.ColumnName = Cast(V.sVal as Integer)
"Mike" wrote:

> I have a stored proc that accepts a varchar as a parameter. What is being
> passed in is one or more IDs in a comma separated list. (ie '123,567,789')
> In the where clause I want to pull are records where a value is IN the lis
t
> being passed in. (ie. WHERE column IN (@.Var))
> When I run this stored proc I get an error because its treating
> '123,567,789' as one varchar value insstead of 3 int values.
> Is there anything I can do to work around this?
> Thanks in advance,
> Mike R
>
>

Problem with Dynamic SQL

I am using Dynamic SQL in stored procedures. This following procedure accepts a parameter @.filter which provides the WHERE clause for the query if there is one. Here is the code:

CREATE PROCEDURE TC3_GetAllJobOrders
(
@.filter NVARCHAR(500)
)
 AS
DECLARE @.sqlString NVARCHAR(500)
IF @.filter IS NOT NULL
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder WHERE @.filter'
EXECUTE sp_executesql @.sqlString, N'@.filter NVARCHAR(500)', @.filter
END
ELSE
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder'
EXECUTE sp_executesql @.sqlString
END
GO

When I pass in null for @.filter, it works fine and returns all rows in the TC3_JobOrder table. However when I pass in a syntactically correct filter I get the following error:

"Line 1: Incorrect syntax near '@.filter'."

Even a trivial filter like "1 = 1" gives me this error. I have changed the query from a SELECT statement to a RAISERROR statement so that it prints out the SQL after the value for @.filter has been injected, and everything is syntactically correct. Any ideas why it would be giving me that error? It all seems right...

Jason PachecoYou can't pass in the WHERE condition in that fashion. You just need to concatenate the variable to your SQL string:

IF @.filter IS NOT NULL
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder WHERE ' + @.filter
EXECUTE sp_executesql @.sqlString
END
ELSE
BEGIN
SET @.sqlString = N'SELECT * FROM TC3_JobOrder'
EXECUTE sp_executesql @.sqlString
END

Terri|||And I should add that this approach is extremely vulnerable to SQL injection. The @.Filter value should NOT be coming directly from user input.

Terri|||"And I should add that this approach is extremely vulnerable to SQL injection."

This is what I was afraid of... I posted previously about avoiding SQL injection and I did a little reading, but didn't find anything good. What can I do to avoid SQL injection?

Basically what I want to do is have filterable datagrids and I am using stored procedures for all of my SQL. So the only way I can see to have filterable datagrids (short of writing a stored procedure for every possible combination of filters) is to write a dynamic SQL statement that passes the WHERE clause in.

Is there a better solution? For instance, one thing I was thinking of is, instead of passing in the filter as one argument, I could pass in each field's filter value seperately. Howere, that would mean the stored procedure would take a ton of arguments, not to mention the fact that everytime the table definition changes, the stored procedure would have to change as well, along with the interface.

Jason Pacheco|||You could possibly do something like this:

SELECT
 *
FROM
 TC3_JobOrder
WHERE
 JobNumber = ISNULL(@.JobNumber,JobNumber) AND
 CustomerNumber = ISNULL(@.CustomerNumber,CustomerNumber) AND
 JobDate BETWEEN ISNULL(@.StartDate,'19000101') AND ISNULL(@.EndDate,GETDATE())

To prevent SQL injection you would need to completely validate the user's input, plus make sure that the SQL user account has limited permissions so that if an injection attack is successful the damange is minimized. This isn't the approach I normally take so I don't have firsthand experience.

But I can share with you something that David Penton advised last year on using Dynamic Where clauses, and I don't think he'd mind me sharing it here:
**************************************************************
Date: Tue, 10 Jun 2003 09:13:39 -0600
From: "David L. Penton"
To: aspnet-databases@.aspadvice.com
Subject: Re: [aspnet-databases] RE: Optional Stored Proc Query Parameters All headers

I have a method which I have been using which I like alot. Let's say you have 3 things you want to filter on, but you need to allow any combination of the three.
For simplicity's sake, let's also say that you require at least one value. I am also leaving a few things out for clarity:

CREATE PROCEDURE dbo.myProc
@.val1 int = NULL
, @.val2 varchar(10) = NULL
, @.val3 decimal(12, 0) = NULL
AS

SET NOCOUNT ON

DECLARE @.sql nvarchar(4000)

-- I require a WHERE clause, but you may want to
-- modify it to not need one
IF @.val1 IS NULL OR @.val2 IS NULL OR @.val3 IS NULL BEGIN
RAISERROR('Must have at least one value for @.val1, @.val2, or @.val3', 11,
1)
RETURN (1)
END

-- make base SQL statement
SET @.sql = N'
SELECT
a.val1, a.val2, a.val3, a.val4, a.val5 FROM
dbo.myTable a
WHERE
1 = 1
@.val1REP
@.val2REP
@.val3REP'

-- make logical replacements
IF @.val1 IS NOT NULL
SET @.sql = REPLACE(@.sql, '@.val1REP', 'AND a.val1 = @.val1') ELSE
SET @.sql = REPLACE(@.sql, '@.val1REP', '')

IF @.val2 IS NOT NULL
SET @.sql = REPLACE(@.sql, '@.val2REP', 'AND a.val2 = @.val2') ELSE
SET @.sql = REPLACE(@.sql, '@.val2REP', '')

IF @.val3 IS NOT NULL
SET @.sql = REPLACE(@.sql, '@.val3REP', 'AND a.val3 = @.val3') ELSE
SET @.sql = REPLACE(@.sql, '@.val3REP', '')

--select @.sql
EXECUTE "dbo"."sp_executesql"
@.sql
, N'@.val1 int, @.val2 varchar(10), @.val3 decimal(12, 0)'
, @.val1 = @.val1
, @.val2 = @.val2
, @.val3 = @.val3

RETURN 0

GO

This has a benefit of:

[] preventing SQL injection because you are using parameterized queries

[] caching query plans because you are using sp_executesql

[] controlling the data coming in to the procedure

I actually would do a little more with the '1=1' part, but for email clarity I am not.
Also, in my queries I typically have values that are always required so the '1=1' isn't necessary.

hth...

David L. Penton, Microsoft MVP SQL Server
JCPenney Technical Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach"
david@.davidpenton.com

Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
New SP3 SQL BOL: http://www.davidpenton.com/sqlbol

**************************************************************

Also, I think this Patterns & Practices is a good read:Improving Web Application Security: Threats and Countermeasures,Building Secure ASP.NET Applications, in particularChapter 14 - Building Secure Data Access.

Terri|||Alright, I went with a parameterized stored procedure similar to what you have proposed. However, I have one remaining problem. One of the values I am passing in is tied directly to a textbox on the frontent and is very subject to SQL injection. What is the best way of validating this control before its value is passed to the sproc?

I thought about modifying permissions, but it's an ASP.NET application so the sproc runs in the context of the ASP.NET user, who already has EXEC permissions to the sproc, and that can't be modified.|||It took me a while to write that reply and it's not as organized as it could be otherwise.
I mixed my message somewhat. If you use parameters SQL injection should not be an issue.

Since I haven't used dynamic SQL which is built directly from user input I have not developed a strategy to handle this myself. I know you should validate input to make sure the data is of the datatype you are expecting. But if you have a freeform textbox where the user can type in " AND City = 'Dallas'" then that is really a gaping hole.

Hopefully others can chime in on this.

Here's a brief synopsis I found from SQL Server MagazineInjection Protection. And I see thatChapter 10 of the link I gave you previously has a lot of meaty information about validating input.

Terri

Problem With Distinct clause

Hi

I have the following tables:

Suppliers:
SupplierID int not null primary key auto_increment
SubscriptionTypeID int not null
SubscriptionExpDate Date

Products:
ID int not null primary key auto_increment
SupplierID int not null as foreign key
ProductCode varchar(30) not null
productName varchar(255) not null
ImageID int not null as foreign key

Images:
ImageID int not null primary key auto_increment
Image blob not null

Whats is the correct SQL syntax to retrieve the disticnt Image and ProductName where ProductName is LIKE some user defined string and SubscriptionExpDate >= Date() order by SubscriptionTypeID DESC.

The above query will retrieve multiple records for the same Image if there are multiple suppliers for that product. How do I retrieve the distinct Image? The only other way i can think of solving this is to redesign the table and include the productName in the Images table. However all images are unique but product names supplied by the supplier for a particular image/product can be different. hence to increase the posibility of retrieving a match I have included it in the products Table!

Any help will be appreciated.what does "the distinct image" mean?

if a single product is supplied by multiple suppliers, and you have a query where you join product to suppliers, then why aren't you asking about returning "the distinct product" too?

that is where your difficulty lies ;)|||Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name. Hence I want to retrieve distinct name as well. But that is not possible becuase the product name for a given product is different for each supplier yet it is for the same product. i dont think there is any other way round this than the way I metioned. By including product name in the images table aswell!

You may be wondering what is the purpose of such a query. well it is to be implemented on a web page. The above query should display all items which match the search string but onyl display unique images and any one name. At present I get duplicate images and names per row.

If you have any other suggestions I would be glad to hear them.|||here's the important part of what you just said --Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name.so my question is, which one? the item with the shortest name? the supplier with the latest registration date?

to pick one from among many, you need a way to say which one

answers that are not allowed include "any one," "the first one," and "you pick one"

:)