Database

SELECT Convert(VarChar(10), IntValue) + VarcharValue .....

SELECT Cast(IntValue, VarChar(10)) + VarcharValue .....

RCoate wrote:

I am already using stored procedures et al, but am now wondering what best practice would be for string replacement in parameters.

For SQL Server 2K5, I am already using:
Replace ' with ''

EXEC MyStoredProcedure @Name='John O''Conner';

RCoate wrote:

But should I extend this to include:

Replace ; with [empty string]Replace drop[space] with [empty string]

Colin Angus Mackay wrote:

Does your SqlCommand.CommandText look like this:

EXEC MyStoredProcedure @Name='John O''Conner';

cmd.CommandText = "SprocName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = [open connection object]

Dim pFirstParameter As SqlParameter = SetParam("@ParamName", ParameterDirection, Datatype, DataSize)
pFirstParameter.Value = theValue
cmd.Parameters.Add(pFirstParameter)

dr = cmd.ExecuteReader

	@FirstParam		varchar(100)
AS

IF EXISTS(SELECT * FROM MyTable WHERE (SearchField=@FirstParam))
	BEGIN
		SELECT
                        Field1,
                        Field2,
                        ...
                        FieldN
		FROM
			MyTable
		WHERE
			SearchField=@FirstParam
	END

RCoate wrote:

My question is really, how far should you take user data sanitation?

<br />
IF GetDate() - '01 may 2008'  > DateAdd(MONTH,3,'1900-01-01 00:00:00.000') <br />
	PRINT('Current Date is more then 3 months from 01 may 2008')<br />
ELSE <br />
	PRINT('Current Date is less then, or equal to, 3 months from 01 may 2008')<br />

<br />
<br />
IF GetDate() - DateAdd(MONTH, 3, '01 may 2008') > 0<br />
	PRINT('Current Date is more then 3 months from 01 may 2008')<br />
ELSE<br />
	PRINT('Current Date is less then, or equal to, 3 months from 01 may 2008')<br />