Making strings safe for SQL?

I'm writing some general SQL functions to save time later and one of the
functions I'm looking for is something to make strings "SQL Friendly".
What I need to know is, what should I be watching out for?
At this point, I'm assuming that everthing is safe, once the following is
applied to a string:
Any " is changed to ""
Any ' is changed to `
...what else is there to worry about?Noozer wrote:
> I'm writing some general SQL functions to save time later and one of the
> functions I'm looking for is something to make strings "SQL Friendly".
> What I need to know is, what should I be watching out for?
> At this point, I'm assuming that everthing is safe, once the following is
> applied to a string:
> Any " is changed to ""
> Any ' is changed to `
> ...what else is there to worry about?
Can you explain a bit about what you are trying to do. Why do you need
a SQL function to do this? If you pass strings as parameters then all
this is taken care of for you. No special handling is required for
quote characters unless you are constructing dynamic SQL code.
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
--|||>> What I need to know is, what should I be watching out for?
> Can you explain a bit about what you are trying to do. Why do you need
> a SQL function to do this? If you pass strings as parameters then all
> this is taken care of for you. No special handling is required for
> quote characters unless you are constructing dynamic SQL code.
Ya.. I guess I should have been a bit more specific.
: )
I'm writing some ASP code that builds SQL commands and queries dynamically.
...and now, thinking about this, I really should build functions to ensure
that numbers are really numbers, etc...|||> I'm writing some ASP code that builds SQL commands and queries
> dynamically.
> ...and now, thinking about this, I really should build functions to ensure
> that numbers are really numbers, etc...
The Best Practice for security is to build parameteritized SQL statements:
Hope this helps.
Dan Guzman
SQL Server MVP
"Noozer" <dont.spam@.me.here> wrote in message
news:%23%23ROjAgZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>
> Ya.. I guess I should have been a bit more specific.
> : )
> I'm writing some ASP code that builds SQL commands and queries
> dynamically.
> ...and now, thinking about this, I really should build functions to ensure
> that numbers are really numbers, etc...
>|||Noozer (dont.spam@.me.here) writes:
is
>
> Ya.. I guess I should have been a bit more specific.
>: )
> I'm writing some ASP code that builds SQL commands and queries
> dynamically.
> ...and now, thinking about this, I really should build functions to ensure
> that numbers are really numbers, etc...
You are barking up the wrong tree entirely. As Dan says, you should
use parameterised strings and never interpolate values into the
SQL. This is easier, safer, and far more performant. (Because the plan
for parameterised query is cached, and can be reused for other
parameter values.)
Here is one sample of a parameterised query using ADO and VB6:
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If
If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append _
cmd.CreateParameter("@.shipname", adVarWChar, adParamInput, 40,
shipname)
End If
Set rs = cmd.Execute
And here is one for VB .Net and SqlClient:
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = " SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)" & _
" FROM dbo.Orders O " & _
" JOIN dbo.[Order Details] OD ON O.OrderID = OD.OrderID" & _
" WHERE O.OrderDate BETWEEN @.from AND @.to" & _
" AND EXISTS (SELECT *" & _
" FROM dbo.[Order Details] OD2" & _
" WHERE O.OrderID = OD2.OrderID" & _
" AND OD.ProductID = @.prodid)" & _
" GROUP BY O.OrderID"
cmd.Parameters.Add("@.from", SqlDbType.Datetime)
cmd.Parameters("@.from").Value = "1998-02-01"
cmd.Parameters.Add("@.to", SqlDbType.Datetime)
cmd.Parameters("@.to").Value = "1998-02-28"
cmd.Parameters.Add("@.prodid", SqlDbType.Int)
cmd.Parameters("@.prodid").Value = 76
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|||Best bet is to research SQL Injection techniques and change your approach.
There are so many ways for a hacker to get into the system if you use
concatenation...
Some basic info (which you seem somewhat familiar with judging by your
question):
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.sqlservercentral.com/col...qlinjection.asp
Some advanced dangers:
http://www.nextgenss.com/papers/adv...l_injection.pdf
http://www.nextgenss.com/papers/mor...l_injection.pdf
Parameters are really the way to go.
"Noozer" <dont.spam@.me.here> wrote in message
news:uP9m4ffZGHA.1192@.TK2MSFTNGP03.phx.gbl...
> I'm writing some general SQL functions to save time later and one of the
> functions I'm looking for is something to make strings "SQL Friendly".
> What I need to know is, what should I be watching out for?
> At this point, I'm assuming that everthing is safe, once the following is
> applied to a string:
> Any " is changed to ""
> Any ' is changed to `
> ...what else is there to worry about?
>sql

Make date from several strings

Hello all

Someone gave me this:

Month Day Year Hour Min Sec
9 9 2006 15 9 36

And I need to make a descent date format out of it which looks like this:

Sun Sept 09 15:09:36 CEST 2006

I can concatenate the whole thing but I'm stuck with the "sunday" part.

I'm sure there are some experst out there who know how to do this is in 1 minute;)

Regards

Worf

to get the name wrap left(convert(varchar(30),dateadd(d,day-1,'19000101'),100),3)

around your Day column

example

select left(convert(varchar(30),dateadd(d,dayCol-1,'19000101'),100),3) from(
select 9 as dayCol, 9 as m, 2006 as y)x

Denis The SQL Menace
http://sqlservercode.blogspot.com/

|||

Exellent!

Many thanks!

Worf

|||

So far I have the following code but it gives me an error:

Conversion failed when converting datetime from character string.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[MakeDate] (@.Month int, @.Day int, @.Year int, @.Hour int, @.Min int, @.Sec int)
RETURNS nvarchar(50)
AS
BEGIN

DECLARE @.C_Date nvarchar(50)
DECLARE @.TempMonth varchar(3)
DECLARE @.TempWeekDay varchar(3)

SET @.TempMonth = LEFT(CONVERT(nvarchar,(DATENAME(month, (@.Year + @.Month + @.Day)))), 3)
SET @.TempWeekDay = LEFT(CONVERT(nvarchar,(DATENAME(weekday, (@.Year + @.Month + @.Day)))),3)
SET @.C_Date = @.TempWeekDay + ' ' + @.TempMonth +' '+ @.Day +' ' + @.Hour +':'+ @.Min +':'+ @.Sec + ' CEST ' + @.Year

RETURN(@.C_Date)

END;

when eexecuting it looks like this:

SELECT [myDatabase].[dbo].[MakeDate] (08,23,2006,12,23,45)

Sigh

Worf

|||

How about this?

CREATE FUNCTION [dbo].[MakeDate] (@.Month int, @.Day int, @.Year int, @.Hour

int, @.Min int, @.Sec int)

RETURNS nvarchar(50)

AS

BEGIN

DECLARE @.C_Date nvarchar(50)

DECLARE @.TempMonth varchar(3)

DECLARE @.TempWeekDay varchar(3)

DECLARE @.MonthNames VARCHAR(100)

DECLARE @.DT datetime

SET @.DT = DATEADD(minute,@.Min,

DATEADD(second,@.Sec,

DATEADD(hour,@.Hour,

DATEADD(day,@.Day,

DATEADD(month,@.Month-1,

DATEADD(year,@.Year-1900,-1))))))

SET @.MonthNames = 'Jan Feb Mar Apr May Jun Jul Aug SeptOct Nov Dec'

DECLARE @.DayNames VARCHAR(100)

SET @.DayNames = 'Sun Mon Tue Wed Thu Fri Sat'

SET @.TempMonth = RTRIM(SUBSTRING(@.MonthNames,(DATEDIFF(month,0,@.DT)%12)*4+1,4))

SET @.TempWeekDay = LEFT(DATENAME(weekday,DATEDIFF(day,0,@.DT)),3)

SET @.C_Date = @.TempWeekDay + ' ' + @.TempMonth +' '

+ RTRIM(DATEPART(Day,@.DT)) +' ' + CONVERT(CHAR(8),@.DT,8)

+ ' CEST ' + RTRIM(DATEPART(Year,@.DT))

RETURN(@.C_Date)

END

GO

SELECT dbo.MakeDate(2,13,2004,11,12,4)

go

This won't work except for English, but Sept is not the

SQL Server short month name, so I didn't use DATENAME

on the month part. This will also accept invalid

parts, and change November 32 to Dec 2, for example.

Test thoroughly!

Steve Kass

Drew University

http://www.stevekass.com

Worf@.discussions.microsoft.com wrote:

> Hello all

>

> Someone gave me this:

>

> Month Day Year Hour Min Sec

> 9 9 2006 15 9 36

>

> And I need to make a descent date format out of it which looks like

> this:

>

> Sun Sept 09 15:09:36 CEST 2006

>

> I can concatenate the whole thing but I'm stuck with the "sunday" part.

>

> I'm sure there are some experst out there who know how to do this is in

> 1 minute;)

>

> Regards

>

> Worf

>

>