Clean input before submitting to database

Is there some recommended way to clean input before submitting it to the database? We'd like to develop a library that can be used on our ASP/ASP.NET apps to filter input before it's sent to the SQL Server and Oracle databases. Is there a way to create a .NET DLL that can be used for both ASP.NET and classic ASP apps. Thanks.What's the meaning of "clean input before submitting it to the database"? Can you explain more? Where does the input locate? If you're talking about input for operations towards database, there seems no way to filter it, since the communication between databases and clients are based request packages. In this case I think it's better to implement this logic on database level, you can use some CONSTRAINTs,TRIGGERs, and so on to prevent 'bad' input to the databas.|||Sorry. Let me clarify. With "clean" I mean free from malicious code. In other words, I'd like to clean it so to prevent SQL injection attacks. I'm implementing the steps outlined in this article:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh04.asp. Thanks.|||Use parameterized queries.sqlsql

Choosing decimal symbol (comma, please! :))

Hello!

I'm having a slight problem figuring out how to make a field in an Access ADP accept input using comma as decimal symbol.

The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv). Both machines have regional settings pointing to Denmark and thus decimal symbol is ','.

When entering data through the interface made as forms in the ADP, I'm forced to enter '.' as decimal symbol for fields defined as DECIMAL(12,2) (for instance). ...This is actually a situation I could live with, if it weren't for the problems I face when trying to base calculus on these fields (doing some input checking).

Instead of resorting to making tedious code to handle the situation I know turn to you guys for help!

Regards ...and THANKS in advance! :)
Ult

NB: As an appetizer I can tell that if I multiply 1.00 with 1.00 I get 10000.00! ...So the problem is quite annoying, if you're not into opening a can of coding-with-a-distinct-feeling-of-this-is-not-quite-the-best-solution! :)http://www.dbforums.com/forumdisplay.php?forumid=84 .. post your question here|||Thanx for replying, Enigma!

I admit the problem has a touch of both worlds (Access/MSSQL) but believe/hope the problem has to do with MS SQL - all my 'pure' Access applications have none of the described problems.

I have of course not found any solution in the Access part of this forum.

U
...Still open for bright ideas! :)|||Btw, if anybody has a way to make insert-statements via Analyser accept decimals written with ',' instead of '.' it might just be part of the solution, I'm looking for!

...Or if anybody could 'kill this idea' by confirming it's not possible (at least without using 'format'-functions...)?|||The setup consists of an Access ADP connected to a SQL2000-server running on W2K(Adv).

Oops .. I missed that (Did not read the entire post :( ) !!!! Let me think about it then !!

Checking for special characters

When passed a last name as input into a stored procedure I need to verify the
last name in a field containing the last name and location. However the last
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches for
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy way
to accomplish this? Thanks in advance.
Gordon
Hi Gordon
"gordon" wrote:

> When passed a last name as input into a stored procedure I need to verify the
> last name in a field containing the last name and location. However the last
> name is the first word starting from the left up until a space or a special
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John

Checking for special characters

When passed a last name as input into a stored procedure I need to verify the
last name in a field containing the last name and location. However the last
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches for
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy way
to accomplish this? Thanks in advance.
GordonHi Gordon
"gordon" wrote:
> When passed a last name as input into a stored procedure I need to verify the
> last name in a field containing the last name and location. However the last
> name is the first word starting from the left up until a space or a special
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John

Checking for special characters

When passed a last name as input into a stored procedure I need to verify th
e
last name in a field containing the last name and location. However the las
t
name is the first word starting from the left up until a space or a special
character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
Thomas*Texas etc). I just want to go through the table and find matches fo
r
"Thomas". I tried to use char index and substring but I recieve the error
"Invalid length parameter passed to the substring function" when checking
for a special character that is not present. Does anyone know of an easy wa
y
to accomplish this? Thanks in advance.
GordonHi Gordon
"gordon" wrote:

> When passed a last name as input into a stored procedure I need to verify
the
> last name in a field containing the last name and location. However the l
ast
> name is the first word starting from the left up until a space or a specia
l
> character like a '-,/,*,_' (i.e. Thomas-Hawaii, Thomas California,
> Thomas*Texas etc). I just want to go through the table and find matches
for
> "Thomas". I tried to use char index and substring but I recieve the error
> "Invalid length parameter passed to the substring function" when checking
> for a special character that is not present. Does anyone know of an easy
way
> to accomplish this? Thanks in advance.
> Gordon
You could use a function to determine the length or the appropriate
substring such as
CREATE FUNCTION dbo.GetFirstName(@.name varchar(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @.returnname varchar(50)
SELECT @.returnname = LEFT(@.name,len)
FROM
( SELECT MIN(len)-1 AS len
FROM
( SELECT CHARINDEX('-',@.name) as LEN
UNION ALL SELECT CHARINDEX('*',@.name)
UNION ALL SELECT CHARINDEX('_',@.name)
UNION ALL SELECT CHARINDEX('/',@.name)
UNION ALL SELECT CHARINDEX(SPACE(1),@.name)
UNION ALL SELECT LEN(@.name)+1 ) A
WHERE len > 0 ) l
RETURN (@.returnname)
END
SELECT dbo.GetFirstName(name)
FROM
( SELECT 'George*Texas' AS name
UNION ALL SELECT 'Thomas-Hawaii'
UNION ALL SELECT 'Arnie California' ) a
John

Check value type before inserting into a table

Is there a way to check the type of a value before inserting the value into
the table? I am working with sql 7 and I need to verify the input values to
a table before inserting them.
For example...
table A, field1 is an integer field type
default value=0;
value//input value from application
if value (future value) is an integer to be inserted into field1 then
insert(value)
else
insert(default value)
This is just a simple logic procedure I want the trigger to check before ins
erting or updating field1You could use isnumeric, e.g.
DECLARE @.def INT
SET @.def = 0
INSERT tbl(col) SELECT CASE WHEN ISNUMERIC(@.param)=1 THEN @.param ELSE @.def
END
Though mind that IsPerfect(IsNumeric())=0.
http://www.aspfaq.com/2390
http://www.aspfaq.com/
(Reverse address to reply.)
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1|||You could use the CASE statement in an insert statement:
create table #test(c char(1), i int)
declare @.x varchar(10)
set @.x = 1
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
set @.x = 'xxx'
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
select * from #test
drop table #test
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1

Check value type before inserting into a table

Is there a way to check the type of a value before inserting the value into the table? I am working with sql 7 and I need to verify the input values to a table before inserting them
For example..
table A, field1 is an integer field typ
default value=0
value//input value from applicatio
if value (future value) is an integer to be inserted into field1 the
insert(value
els
insert(default value
This is just a simple logic procedure I want the trigger to check before inserting or updating field1You could use isnumeric, e.g.
DECLARE @.def INT
SET @.def = 0
INSERT tbl(col) SELECT CASE WHEN ISNUMERIC(@.param)=1 THEN @.param ELSE @.def
END
Though mind that IsPerfect(IsNumeric())=0.
http://www.aspfaq.com/2390
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1|||You could use the CASE statement in an insert statement:
create table #test(c char(1), i int)
declare @.x varchar(10)
set @.x = 1
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
set @.x = 'xxx'
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
select * from #test
drop table #test
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1

Check value type before inserting into a table

Is there a way to check the type of a value before inserting the value into the table? I am working with sql 7 and I need to verify the input values to a table before inserting them.
For example...
table A, field1 is an integer field type
default value=0;
value//input value from application
if value (future value) is an integer to be inserted into field1 then
insert(value)
else
insert(default value)
This is just a simple logic procedure I want the trigger to check before inserting or updating field1
You could use isnumeric, e.g.
DECLARE @.def INT
SET @.def = 0
INSERT tbl(col) SELECT CASE WHEN ISNUMERIC(@.param)=1 THEN @.param ELSE @.def
END
Though mind that IsPerfect(IsNumeric())=0.
http://www.aspfaq.com/2390
http://www.aspfaq.com/
(Reverse address to reply.)
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1
|||You could use the CASE statement in an insert statement:
create table #test(c char(1), i int)
declare @.x varchar(10)
set @.x = 1
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
set @.x = 'xxx'
insert into #test values('a', case when isnumeric(@.x) = 1 then @.x else 0
end)
select * from #test
drop table #test
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carlos Aguero" <carlos986@.hotmail.com> wrote in message
news:C1FDEFB3-88D3-4EA7-8A24-3B663EEF71EE@.microsoft.com...
> Is there a way to check the type of a value before inserting the value
into the table? I am working with sql 7 and I need to verify the input
values to a table before inserting them.
> For example...
> table A, field1 is an integer field type
> default value=0;
> value//input value from application
> if value (future value) is an integer to be inserted into field1 then
> insert(value)
> else
> insert(default value)
> This is just a simple logic procedure I want the trigger to check before
inserting or updating field1

Check sql table input at certian times and send email

I have a .net web application and need to create a program that needs to check the database every two hours and if data has not been input send the user an email message. I have heard this can be done via SQL Server - events or triggers? Can anyone tell me where I can find example code or more information on this. Thank you in advance.

Hi,

Yes since your whole work is within the database then I think setting up a "SQL job" will be the best possible solution also you can schedule it to run on certain times. Basically you need to create a "stored procedure" which will check the table and will be sending mail and you can call this stored procedure from your job. Mails can be sent by using "SQL Mail" or using .NET framework (if you are using Sql Server 2005).

I have marked the importent terms in the mail. You can get best materials on them in Books Online in Sql Server.

Hope it helps!

Bhaskar!

|||

In a well trafficed website, I usually drop that in the login.aspx page, or if I have enabled cookies, then another often hit page.

You can also do this using sql agent. Write the stored procedure to scan the table, and generate the emails. Then tell sql agent to execute that stored procedure every two hours.