Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > INSERT script to SQL Server

Reply
Thread Tools

INSERT script to SQL Server

 
 
teddysnips@hotmail.com
Guest
Posts: n/a
 
      02-14-2008
A friend has been tasked with designing a website. It all looks very
swish, but he's been asked to add a page to allow people to register
for events etc. He wants to add some script to save these details to
a SQL Server database. Could someone please take a brief look at the
script below and tell me if this looks ok?

Thanks

Edward

<%
Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
Info=True;User ID=uid;Initial Catalog=Test;Data
Source=YOURSERVERHERE;"

sqlstring = "INSERT INTO UserDetails (UserName, Tel, Email) VALUES
('"

sqltemp = document.getElementById('UserName').value
sqlstring = sqlstring + sqltemp + "', '"

sqltemp = document.getElementById('Tel').value
sqlstring = sqlstring + sqltemp + "', '"

sqltemp = document.getElementById('Email').value
sqlstring = sqlstring + sqltemp + "', ')"

conn.execute(sqlstring)
conn.close
set conn = nothing
%>
 
Reply With Quote
 
 
 
 
Evertjan.
Guest
Posts: n/a
 
      02-14-2008
wrote on 14 feb 2008 in microsoft.public.inetserver.asp.general:

> A friend has been tasked with designing a website. It all looks very
> swish, but he's been asked to add a page to allow people to register
> for events etc. He wants to add some script to save these details to
> a SQL Server database. Could someone please take a brief look at the
> script below and tell me if this looks ok?
>
> Thanks
>
> Edward
>
> <%
> Set Conn = Server.CreateObject("ADODB.Connection")
>
> Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
> Info=True;User ID=uid;Initial Catalog=Test;Data
> Source=YOURSERVERHERE;"
>
> sqlstring = "INSERT INTO UserDetails (UserName, Tel, Email) VALUES
> ('"
>
> sqltemp = document.getElementById('UserName').value
> sqlstring = sqlstring + sqltemp + "', '"


This would be very dangerous on the web.
Read up on SQL injection.
Always validate data comming from clientside against this.
Bob even has a better alternative that I never use.

> sqltemp = document.getElementById('Tel').value
> sqlstring = sqlstring + sqltemp + "', '"
>
> sqltemp = document.getElementById('Email').value
> sqlstring = sqlstring + sqltemp + "', ')"
>
> conn.execute(sqlstring)
> conn.close
> set conn = nothing
> %>
>




--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-14-2008
http://www.velocityreviews.com/forums/(E-Mail Removed) wrote:
> A friend has been tasked with designing a website. It all looks very
> swish, but he's been asked to add a page to allow people to register
> for events etc. He wants to add some script to save these details to
> a SQL Server database. Could someone please take a brief look at the
> script below and tell me if this looks ok?
>
> Thanks
>
> Edward
>
> <%
> Set Conn = Server.CreateObject("ADODB.Connection")
>
> Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
> Info=True;User ID=uid;Initial Catalog=Test;Data
> Source=YOURSERVERHERE;"
>
> sqlstring = "INSERT INTO UserDetails (UserName, Tel, Email) VALUES
> ('"
>
> sqltemp = document.getElementById('UserName').value
> sqlstring = sqlstring + sqltemp + "', '"


& should be used for string concatenation, not +.

>
> sqltemp = document.getElementById('Tel').value
> sqlstring = sqlstring + sqltemp + "', '"
>
> sqltemp = document.getElementById('Email').value
> sqlstring = sqlstring + sqltemp + "', ')"
>
> conn.execute(sqlstring)
> conn.close
> set conn = nothing
> %>


Your friend's use of dynamic sql is leaving you vulnerable to hackers using
sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e


Personally, I prefer using stored procedures:
http://groups.google.com/group/micro...9dc1701?hl=en&

Using parameters is only one weapon that should be used to counter sql
injection: data validation should also be performed. Do not depend on
client-side validation. Always validate user input in server-side code

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
teddysnips@hotmail.com
Guest
Posts: n/a
 
      02-14-2008
On 14 Feb, 12:26, "Bob Barrows [MVP]" <(E-Mail Removed)>
wrote:
[...]
> Your friend's use of dynamic sql is leaving you vulnerable to hackers using
> sql
> injection:http://mvp.unixwiz.net/techtips/sql-....aspx?tabid=23
>
> See here for a better, more secure way to execute your queries by using
> parameter markers:http://groups-beta.google.com/group/...etserver.asp.d...
>
> Personally, I prefer using stored procedures:http://groups.google.com/group/micro...ver.asp.genera...
>
> Using parameters is only one weapon that should be used to counter sql
> injection: data validation should also be performed. Do not depend on
> client-side validation. Always validate user input in server-side code


Thanks. I've taken a look at the sites you suggest and have come up
with two possibilities derived from the various suggestions:

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
Info=True;User ID=uid;Initial
Catalog=Test;Data Source=YOURSERVERHERE;"

sqlstring = "INSERT INTO UserDetails (UserName, Email) VALUES
(@UserName, @Email)"
cmd = new SqlCommand(sqlstring)
cmd.Connection = Conn

' Add UserName to the above defined paramter @UserName
cmd.Parameters.Add(
new SqlParameter(
"@UserName", System.Data.SqlDbType.NVarChar, 255, "UserName"))

cmd.Parameters["@UserName"].Value =
document.getElementById('UserName').value

' Add Email to the above defined paramter @Email
cmd.Parameters.Add(
new SqlParameter(
"@Email", System.Data.SqlDbType.NVarChar, 100, "Email"))

cmd.Parameters["@Email"].Value =
document.getElementById('Email').value

' Execute the query
cmd.ExecuteReader()

conn.close
set conn = nothing
set cmd = nothing
%>

OR

<%

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
Info=True;User ID=uid;Initial
Catalog=Test;Data Source=YOURSERVERHERE;"

strSQL = "exec strMySPROC @UserName='" &
document.getElementById('Email').value & "', @Email= '" &
document.getElementById('Email').value & '"


conn.Execute(strSQL)
%>

Am I doing this right?

Thanks

Edward
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-14-2008
(E-Mail Removed) wrote:
> On 14 Feb, 12:26, "Bob Barrows [MVP]" <(E-Mail Removed)>
> wrote:
> [...]
>> Your friend's use of dynamic sql is leaving you vulnerable to
>> hackers using
>> sql
>> injection:http://mvp.unixwiz.net/techtips/sql-....aspx?tabid=23
>>
>> See here for a better, more secure way to execute your queries by
>> using
>> parameter
>> markers:http://groups-beta.google.com/group/...etserver.asp.d...
>>
>> Personally, I prefer using stored
>> procedures:http://groups.google.com/group/micro...ver.asp.genera...
>>
>> Using parameters is only one weapon that should be used to counter
>> sql
>> injection: data validation should also be performed. Do not depend on
>> client-side validation. Always validate user input in server-side
>> code

>
> Thanks. I've taken a look at the sites you suggest and have come up
> with two possibilities derived from the various suggestions:


None of my examples resemble what follows. But continue.

>
> <%
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
> Info=True;User ID=uid;Initial
> Catalog=Test;Data Source=YOURSERVERHERE;"
>
> sqlstring = "INSERT INTO UserDetails (UserName, Email) VALUES
> (@UserName, @Email)"



The above statements are vbscript code. Now, for some strange reason, you
switch to VB.Net??

> cmd = new SqlCommand(sqlstring)
> cmd.Connection = Conn


You cannot assign an ADODB connection object to a .Net SQLCommand object.

>
> ' Add UserName to the above defined paramter @UserName
> cmd.Parameters.Add(
> new SqlParameter(
> "@UserName", System.Data.SqlDbType.NVarChar, 255, "UserName"))
>
> cmd.Parameters["@UserName"].Value =
> document.getElementById('UserName').value
>
> ' Add Email to the above defined paramter @Email
> cmd.Parameters.Add(
> new SqlParameter(
> "@Email", System.Data.SqlDbType.NVarChar, 100, "Email"))
>
> cmd.Parameters["@Email"].Value =
> document.getElementById('Email').value
>
> ' Execute the query
> cmd.ExecuteReader()
>
> conn.close
> set conn = nothing
> set cmd = nothing
> %>


???
You are mixing vbscript and VB.Net. They are not the same!!!

>
> OR
>
> <%
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
> Info=True;User ID=uid;Initial
> Catalog=Test;Data Source=YOURSERVERHERE;"
>
> strSQL = "exec strMySPROC @UserName='" &
> document.getElementById('Email').value & "', @Email= '" &
> document.getElementById('Email').value & '"
>


No, this is just as bad as your original example. You have created a stored
procedure and undone your good work by using dynamic sql to execute it!! The
data in that xml document is the result of user input, is it not? What if a
malicious user input something like this into the email field:

';truncate table userdetails; --

Using concatenation, that statement would get executed.

Go back and read
http://groups.google.com/group/micro...9dc1701?hl=en&

>
> conn.Execute(strSQL)
> %>
>
> Am I doing this right?
>

No. First of all, you need to decide it you are creating an ASP.Net page
(its extension would be .aspx) or a classic asp page (its extension would be
..asp). If you are doing ASP.Net, then this is not the correct newsgroup: try
microsoft.public.dotnet.framework.aspnet or the forums at www.asp.net.

If you are doing classic asp, then you need to go back and reread the
examples I posted.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
teddysnips@hotmail.com
Guest
Posts: n/a
 
      02-15-2008
On 14 Feb, 16:54, "Bob Barrows [MVP]" <(E-Mail Removed)>
wrote:

[...]

> If you are doing classic asp, then you need to go back and reread the
> examples I posted.


Ok, third time lucky! By the way, thanks for your patience. You're
clearly an adherent of the "teach a man to fish" school ;}

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
Info=True;User ID=uid;Initial
Catalog=Test;Data Source=YOURSERVERHERE;"

strUserName = document.getElementById('UserName').value
strEmail = document.getElementById('Email').value
conn.stpMyProcedure strUserName, strEmail

conn.close
set conn = nothing

%>
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-15-2008
(E-Mail Removed) wrote:
> On 14 Feb, 16:54, "Bob Barrows [MVP]" <(E-Mail Removed)>
> wrote:
>
> [...]
>
>> If you are doing classic asp, then you need to go back and reread the
>> examples I posted.

>
> Ok, third time lucky! By the way, thanks for your patience. You're
> clearly an adherent of the "teach a man to fish" school ;}
>
> <%
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
> Info=True;User ID=uid;Initial
> Catalog=Test;Data Source=YOURSERVERHERE;"
>
> strUserName = document.getElementById('UserName').value
> strEmail = document.getElementById('Email').value
> conn.stpMyProcedure strUserName, strEmail
>
> conn.close
> set conn = nothing
>
> %>


There you go - you're halfway there! The other half is to make sure nothing
invalid or malicious is in those strUserName and strEmail variables BEFORE
opening the database connection. Again, don't depend on client-side
validation. The place to perform validation is right here in the server-side
code.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
teddysnips@hotmail.com
Guest
Posts: n/a
 
      02-15-2008
On 15 Feb, 11:17, "Bob Barrows [MVP]" <(E-Mail Removed)>
wrote:
> (E-Mail Removed) wrote:
> > On 14 Feb, 16:54, "Bob Barrows [MVP]" <(E-Mail Removed)>
> > wrote:

>
> > [...]

>
> >> If you are doing classic asp, then you need to go back and reread the
> >> examples I posted.

>
> > Ok, third time lucky! *By the way, thanks for your patience. *You're
> > clearly an adherent of the "teach a man to fish" school ;}

>
> > <%
> > * *Set Conn = Server.CreateObject("ADODB.Connection")
> > * *Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
> > Info=True;User ID=uid;Initial
> > * * * Catalog=Test;Data Source=YOURSERVERHERE;"

>
> > * *strUserName = document.getElementById('UserName').value
> > * *strEmail = document.getElementById('Email').value
> > * *conn.stpMyProcedure strUserName, strEmail

>
> > * *conn.close
> > * *set conn = nothing

>
> > %>

>
> There you go - you're halfway there! The other half is to make sure nothing
> invalid or malicious is in those strUserName and strEmail variables BEFORE
> opening the database connection. Again, don't depend on client-side
> validation. The place to perform validation is right here in the server-side
> code.


We're only concerned here with text - there are no numeric or date
fields. Is it sufficient simply to pass strings through a function
like:

FUNCTION FixString(str)
FixString = replace(str,"'","''")
END FUNCTION


As ever, I'm very grateful for the time and patience you have shown -
thanks

Edward
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      02-15-2008
(E-Mail Removed) wrote:
> On 15 Feb, 11:17, "Bob Barrows [MVP]" <(E-Mail Removed)>
> wrote:
>> (E-Mail Removed) wrote:
>>> On 14 Feb, 16:54, "Bob Barrows [MVP]" <(E-Mail Removed)>
>>> wrote:

>>
>>> [...]

>>
>>>> If you are doing classic asp, then you need to go back and reread
>>>> the examples I posted.

>>
>>> Ok, third time lucky! By the way, thanks for your patience. You're
>>> clearly an adherent of the "teach a man to fish" school ;}

>>
>>> <%
>>> Set Conn = Server.CreateObject("ADODB.Connection")
>>> Conn.Open "Provider=SQLOLEDB.1;Password=pwd;Persist Security
>>> Info=True;User ID=uid;Initial
>>> Catalog=Test;Data Source=YOURSERVERHERE;"

>>
>>> strUserName = document.getElementById('UserName').value
>>> strEmail = document.getElementById('Email').value
>>> conn.stpMyProcedure strUserName, strEmail

>>
>>> conn.close
>>> set conn = nothing

>>
>>> %>

>>
>> There you go - you're halfway there! The other half is to make sure
>> nothing invalid or malicious is in those strUserName and strEmail
>> variables BEFORE opening the database connection. Again, don't
>> depend on client-side validation. The place to perform validation is
>> right here in the server-side code.

>
> We're only concerned here with text - there are no numeric or date
> fields. Is it sufficient simply to pass strings through a function
> like:
>
> FUNCTION FixString(str)
> FixString = replace(str,"'","''")
> END FUNCTION
>
>
>

No, this step is absolutely not necessary when using parameters: it will
only result in your storing double apostrophes in the database. People
sometimes think that this type of function makes their dynamic sql safe, but
experienced hackers can easily get past this protection.

What I am talking about is, in addition to verifying email addresses are in
the proper format: also check the user input for malicious hack attempts:
check the data for the presence of sql keywords and function names that
absolutely should not be there (DELETE, TRUNCATE, DROP, xp_cmdshell, etc.).
How you wish to respond to such attempts is up to you. But there is no need
to allow the insertion of such hack attempts into your database, is there?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
teddysnips@hotmail.com
Guest
Posts: n/a
 
      02-15-2008
On 15 Feb, 12:55, "Bob Barrows [MVP]" <(E-Mail Removed)>
wrote:
[...]
> What I am talking about is, in addition to verifying email addresses are in
> the proper format: also check the user input for malicious hack attempts:
> check the data for the presence of sql keywords and function names that
> absolutely should not be there (DELETE, TRUNCATE, DROP, xp_cmdshell, etc.).
> How you wish to respond to such attempts is up to you. But there is no need
> to allow the insertion of such hack attempts into your database, is there?


I agree, but I have some reservations. I've written other systems
where e-mail addresses need to be validated and I'm sure I can dig out
a reliable RegExp for this. But is there a finite number of sql
keywords and function names that need to be checked? The ingenuity of
the cracker community presumably means that we need a pretty robust
approach. In all the sites and pages you've helpfully provided, I
haven't seen any that explain how to go about checking for the
presence of potentially dangerous words. For example, thinking on the
fly, what if your surname was Dropman - ok, I've never met anyone
called that, but it's not impossible?

Thanks

Edward
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help. Getting a An error has occurred while establishing a connectionto the server. When connecting to SQL Server 2005, this failure may be causedby the fact that under the default settings SQL Server does not allow remote aboutjav.com@gmail.com ASP .Net 0 05-03-2008 12:43 PM
insert python script in current script Prashant Python 2 04-18-2008 07:16 PM
Do the Self-Paced Training Kits: Microsoft SQL Server 2000 include Eval copy of SQL Server? Brian Whiting Microsoft Certification 2 12-29-2005 04:24 AM
Tools to extract data from SQL database and convert it into XML & insert XML data into SQL databases Harry Zoroc XML 1 07-12-2004 10:10 PM
insert a script element into the document body from an external script.... Brian Javascript 2 06-24-2004 05:25 PM



Advertisments