Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert single quotes from web form into SQL database

Status
Not open for further replies.
yaknowss

yaknowss

Programmer
Apr 19, 2012
69
US
I'm sure this is fairly simple to do, but, given the code I am working with I am having trouble... For example, if someone types John's in one of the fields, I would like for it to show up as John's in the database. Any help is greatly appreciated. Right now we cannot insert single quotes when submitting a form.

Code: 
<!--#INCLUDE FILE = "header.asp" -->
<% if classid = "" then %>
<p align="center">Invalid evaluation request.</p>
<% else
sql="INSERT INTO "&results&" (StudentName,EmailAddress,ClassID,ClassName,ClassLevel,EvalDate,MidEndYear,RecommendRepeat,LevelNextYear,InstructorID,Instructor,InstructorB,InstructorTJ,InstructorM,BoysOnly,Reevaluate,A1A,A1B,A1C,A1D,A1E,A1F,A1G,A1H,A1CO,A2A,A2B,A2C,A2D,A2E,A2F,A3A,A3B,A3C,A3D,A3E,A3F,A3G,A3H,A3CO,A4A,A4B,A5A,A5B,A5C,A5D,A5E,A5F,A5G,A5H,A5CO,B1A,B1B,B1C,B1D,B1E,B1F,B1G,B1H,B1I,B1J,B1K,B1L,B1M,B1N,B1O,B1P,B1Q,B1CO,B2A,B2B,B2C,B2D,B2E,B2CO,B3A,B3B,B3C,B3D,B3E,B3F,B3G,B3H,B3I,B3J,B3K,B3CO,B4A,B4B,B4C,B4D,B4E,B4F,B4G,B4H,B4I,B4J,B4K,B4L,B4M,B4N,B4CO,C1A,C1B,C1C,C1D,C1E,C1F,C1G,C1CO,C2A,C2B,C2C,C2D,C3A,C3B,C3C,C3D,C3E,C3F,C3G,C3H,C3CO,C4A,C4B,C5A,C5B,C5C,C5D,C5E,C5F,C5G,C5H,C5I,C5CO,U6A,U6B,U6C,U6D,U6E,U6CO) VALUES "
sql=sql & "('" & Request.Form("StudentName") & "',"
sql=sql & "'" & Request.Form("EmailAddress") & "',"
sql=sql & "'" & Request.Form("ClassID") & "',"
sql=sql & "'" & Request.Form("ClassName") & "',"
sql=sql & "'" & Request.Form("ClassLevel") & "',"
sql=sql & "'" & Request.Form("EvalDate") & "',"
sql=sql & "'" & Request.Form("MidEndYear") & "',"
sql=sql & "'" & Request.Form("RecommendRepeat") & "',"
sql=sql & "'" & Request.Form("LevelNextYear") & "',"
sql=sql & "'" & Request.Form("InstructorID") & "',"
sql=sql & "'" & Request.Form("Instructor") & "',"
sql=sql & "'" & Request.Form("InstructorB") & "',"
sql=sql & "'" & Request.Form("InstructorTJ") & "',"
sql=sql & "'" & Request.Form("InstructorM") & "',"
sql=sql & "'" & Request.Form("BoysOnly") & "',"
sql=sql & "'" & Request.Form("Reevaluate") & "',"
'if Request.Form("ClassID") = "1" then
sql=sql & "'" & int(Request.Form("A1A")) + int(Request.Form("A1AC")) & "',"
sql=sql & "'" & int(Request.Form("A1B")) + int(Request.Form("A1BC")) & "',"
sql=sql & "'" & int(Request.Form("A1C")) + int(Request.Form("A1CC")) & "',"
sql=sql & "'" & int(Request.Form("A1D")) + int(Request.Form("A1DC")) & "',"
sql=sql & "'" & int(Request.Form("A1E")) + int(Request.Form("A1EC")) & "',"
sql=sql & "'" & int(Request.Form("A1F")) + int(Request.Form("A1FC")) & "',"
sql=sql & "'" & int(Request.Form("A1G")) + int(Request.Form("A1GC")) & "',"
sql=sql & "'" & int(Request.Form("A1H")) + int(Request.Form("A1HC")) & "',"
sql=sql & "'" & Request.Form("A1CO") & "',"
sql=sql & "'" & int(Request.Form("A2A")) + int(Request.Form("A2AC")) & "',"
sql=sql & "'" & int(Request.Form("A2B")) + int(Request.Form("A2BC")) & "',"
sql=sql & "'" & int(Request.Form("A2C")) + int(Request.Form("A2CC")) & "',"
sql=sql & "'" & int(Request.Form("A2D")) + int(Request.Form("A2DC")) & "',"
sql=sql & "'" & int(Request.Form("A2E")) + int(Request.Form("A2EC")) & "',"
sql=sql & "'" & int(Request.Form("A2F")) + int(Request.Form("A2FC")) & "',"
sql=sql & "'" & int(Request.Form("A3A")) + int(Request.Form("A3AC")) & "',"
sql=sql & "'" & int(Request.Form("A3B")) + int(Request.Form("A3BC")) & "',"
sql=sql & "'" & int(Request.Form("A3C")) + int(Request.Form("A3CC")) & "',"
sql=sql & "'" & int(Request.Form("A3D")) + int(Request.Form("A3DC")) & "',"
sql=sql & "'" & int(Request.Form("A3E")) + int(Request.Form("A3EC")) & "',"
sql=sql & "'" & int(Request.Form("A3F")) + int(Request.Form("A3FC")) & "',"
sql=sql & "'" & int(Request.Form("A3G")) + int(Request.Form("A3GC")) & "',"
sql=sql & "'" & int(Request.Form("A3H")) + int(Request.Form("A3HC")) & "',"
sql=sql & "'" & Request.Form("A3CO") & "',"
sql=sql & "'" & int(Request.Form("A4A")) + int(Request.Form("A4AC")) & "',"
sql=sql & "'" & int(Request.Form("A4B")) + int(Request.Form("A4BC")) & "',"
sql=sql & "'" & int(Request.Form("A5A")) + int(Request.Form("A5AC")) & "',"
sql=sql & "'" & int(Request.Form("A5B")) + int(Request.Form("A5BC")) & "',"
sql=sql & "'" & int(Request.Form("A5C")) + int(Request.Form("A5CC")) & "',"
sql=sql & "'" & int(Request.Form("A5D")) + int(Request.Form("A5DC")) & "',"
sql=sql & "'" & int(Request.Form("A5E")) + int(Request.Form("A5EC")) & "',"
sql=sql & "'" & int(Request.Form("A5F")) + int(Request.Form("A5FC")) & "',"
sql=sql & "'" & int(Request.Form("A5G")) + int(Request.Form("A5GC")) & "',"
sql=sql & "'" & int(Request.Form("A5H")) + int(Request.Form("A5HC")) & "',"
sql=sql & "'" & Request.Form("A5CO") & "',"
'elseif Request.Form("ClassID") = "2" then
sql=sql & "'" & int(Request.Form("B1A")) + int(Request.Form("B1AC")) & "',"
sql=sql & "'" & int(Request.Form("B1B")) + int(Request.Form("B1BC")) & "',"
sql=sql & "'" & int(Request.Form("B1C")) + int(Request.Form("B1CC")) & "',"
sql=sql & "'" & int(Request.Form("B1D")) + int(Request.Form("B1DC")) & "',"
sql=sql & "'" & int(Request.Form("B1E")) + int(Request.Form("B1EC")) & "',"
sql=sql & "'" & int(Request.Form("B1F")) + int(Request.Form("B1FC")) & "',"
sql=sql & "'" & int(Request.Form("B1G")) + int(Request.Form("B1GC")) & "',"
sql=sql & "'" & int(Request.Form("B1H")) + int(Request.Form("B1HC")) & "',"
sql=sql & "'" & int(Request.Form("B1I")) + int(Request.Form("B1IC")) & "',"
sql=sql & "'" & int(Request.Form("B1J")) + int(Request.Form("B1JC")) & "',"
sql=sql & "'" & int(Request.Form("B1K")) + int(Request.Form("B1KC")) & "',"
sql=sql & "'" & int(Request.Form("B1L")) + int(Request.Form("B1LC")) & "',"
sql=sql & "'" & int(Request.Form("B1M")) + int(Request.Form("B1MC")) & "',"
sql=sql & "'" & int(Request.Form("B1N")) + int(Request.Form("B1NC")) & "',"
sql=sql & "'" & int(Request.Form("B1O")) + int(Request.Form("B1OC")) & "',"
sql=sql & "'" & int(Request.Form("B1P")) + int(Request.Form("B1PC")) & "',"
sql=sql & "'" & int(Request.Form("B1Q")) + int(Request.Form("B1QC")) & "',"
sql=sql & "'" & Request.Form("B1CO") & "',"
sql=sql & "'" & int(Request.Form("B2A")) + int(Request.Form("B2AC")) & "',"
sql=sql & "'" & int(Request.Form("B2B")) + int(Request.Form("B2BC")) & "',"
sql=sql & "'" & int(Request.Form("B2C")) + int(Request.Form("B2CC")) & "',"
sql=sql & "'" & int(Request.Form("B2D")) + int(Request.Form("B2DC")) & "',"
sql=sql & "'" & int(Request.Form("B2E")) + int(Request.Form("B2EC")) & "',"
sql=sql & "'" & Request.Form("B2CO") & "',"
sql=sql & "'" & int(Request.Form("B3A")) + int(Request.Form("B3AC")) & "',"
sql=sql & "'" & int(Request.Form("B3B")) + int(Request.Form("B3BC")) & "',"
sql=sql & "'" & int(Request.Form("B3C")) + int(Request.Form("B3CC")) & "',"
sql=sql & "'" & int(Request.Form("B3D")) + int(Request.Form("B3DC")) & "',"
sql=sql & "'" & int(Request.Form("B3E")) + int(Request.Form("B3EC")) & "',"
sql=sql & "'" & int(Request.Form("B3F")) + int(Request.Form("B3FC")) & "',"
sql=sql & "'" & int(Request.Form("B3G")) + int(Request.Form("B3GC")) & "',"
sql=sql & "'" & int(Request.Form("B3H")) + int(Request.Form("B3HC")) & "',"
sql=sql & "'" & int(Request.Form("B3I")) + int(Request.Form("B3IC")) & "',"
sql=sql & "'" & int(Request.Form("B3J")) + int(Request.Form("B3JC")) & "',"
sql=sql & "'" & int(Request.Form("B3K")) + int(Request.Form("B3KC")) & "',"
sql=sql & "'" & Request.Form("B3CO") & "',"
sql=sql & "'" & int(Request.Form("B4A")) + int(Request.Form("B4AC")) & "',"
sql=sql & "'" & int(Request.Form("B4B")) + int(Request.Form("B4BC")) & "',"
sql=sql & "'" & int(Request.Form("B4C")) + int(Request.Form("B4CC")) & "',"
sql=sql & "'" & int(Request.Form("B4D")) + int(Request.Form("B4DC")) & "',"
sql=sql & "'" & int(Request.Form("B4E")) + int(Request.Form("B4EC")) & "',"
sql=sql & "'" & int(Request.Form("B4F")) + int(Request.Form("B4FC")) & "',"
sql=sql & "'" & int(Request.Form("B4G")) + int(Request.Form("B4GC")) & "',"
sql=sql & "'" & int(Request.Form("B4H")) + int(Request.Form("B4HC")) & "',"
sql=sql & "'" & int(Request.Form("B4I")) + int(Request.Form("B4IC")) & "',"
sql=sql & "'" & int(Request.Form("B4J")) + int(Request.Form("B4JC")) & "',"
sql=sql & "'" & int(Request.Form("B4K")) + int(Request.Form("B4KC")) & "',"
sql=sql & "'" & int(Request.Form("B4L")) + int(Request.Form("B4LC")) & "',"
sql=sql & "'" & int(Request.Form("B4M")) + int(Request.Form("B4MC")) & "',"
sql=sql & "'" & int(Request.Form("B4N")) + int(Request.Form("B4NC")) & "',"
sql=sql & "'" & Request.Form("B4CO") & "',"
'elseif Request.Form("ClassID") = "3" then
sql=sql & "'" & int(Request.Form("C1A")) + int(Request.Form("C1AC")) & "',"
sql=sql & "'" & int(Request.Form("C1B")) + int(Request.Form("C1BC")) & "',"
sql=sql & "'" & int(Request.Form("C1C")) + int(Request.Form("C1CC")) & "',"
sql=sql & "'" & int(Request.Form("C1D")) + int(Request.Form("C1DC")) & "',"
sql=sql & "'" & int(Request.Form("C1E")) + int(Request.Form("C1EC")) & "',"
sql=sql & "'" & int(Request.Form("C1F")) + int(Request.Form("C1FC")) & "',"
sql=sql & "'" & int(Request.Form("C1G")) + int(Request.Form("C1GC")) & "',"
sql=sql & "'" & Request.Form("C1CO") & "',"
sql=sql & "'" & int(Request.Form("C2A")) + int(Request.Form("C2AC")) & "',"
sql=sql & "'" & int(Request.Form("C2B")) + int(Request.Form("C2BC")) & "',"
sql=sql & "'" & int(Request.Form("C2C")) + int(Request.Form("C2CC")) & "',"
sql=sql & "'" & int(Request.Form("C2D")) + int(Request.Form("C2DC")) & "',"
sql=sql & "'" & int(Request.Form("C3A")) + int(Request.Form("C3AC")) & "',"
sql=sql & "'" & int(Request.Form("C3B")) + int(Request.Form("C3BC")) & "',"
sql=sql & "'" & int(Request.Form("C3C")) + int(Request.Form("C3CC")) & "',"
sql=sql & "'" & int(Request.Form("C3D")) + int(Request.Form("C3DC")) & "',"
sql=sql & "'" & int(Request.Form("C3E")) + int(Request.Form("C3EC")) & "',"
sql=sql & "'" & int(Request.Form("C3F")) + int(Request.Form("C3FC")) & "',"
sql=sql & "'" & int(Request.Form("C3G")) + int(Request.Form("C3GC")) & "',"
sql=sql & "'" & int(Request.Form("C3H")) + int(Request.Form("C3HC")) & "',"
sql=sql & "'" & Request.Form("C3CO") & "',"
sql=sql & "'" & int(Request.Form("C4A")) + int(Request.Form("C4AC")) & "',"
sql=sql & "'" & int(Request.Form("C4B")) + int(Request.Form("C4BC")) & "',"
sql=sql & "'" & int(Request.Form("C5A")) + int(Request.Form("C5AC")) & "',"
sql=sql & "'" & int(Request.Form("C5B")) + int(Request.Form("C5BC")) & "',"
sql=sql & "'" & int(Request.Form("C5C")) + int(Request.Form("C5CC")) & "',"
sql=sql & "'" & int(Request.Form("C5D")) + int(Request.Form("C5DC")) & "',"
sql=sql & "'" & int(Request.Form("C5E")) + int(Request.Form("C5EC")) & "',"
sql=sql & "'" & int(Request.Form("C5F")) + int(Request.Form("C5FC")) & "',"
sql=sql & "'" & int(Request.Form("C5G")) + int(Request.Form("C5GC")) & "',"
sql=sql & "'" & int(Request.Form("C5H")) + int(Request.Form("C5HC")) & "',"
sql=sql & "'" & int(Request.Form("C5I")) + int(Request.Form("C5IC")) & "',"
sql=sql & "'" & Request.Form("C5CO") & "',"
'end if
sql=sql & "'" & Request.Form("U6A") & "',"
sql=sql & "'" & Request.Form("U6B") & "',"
sql=sql & "'" & Request.Form("U6C") & "',"
sql=sql & "'" & Request.Form("U6D") & "',"
sql=sql & "'" & Request.Form("U6E") & "',"
sql=sql & "'" & Request.Form("U6CO") & "')"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnection
objConn.Execute (sql)
objConn.Close
set objConn = Nothing

set rsgetevalid = Server.CreateObject("ADODB.recordset")
strSQLgetevalid = "SELECT * FROM " & results & " WHERE StudentName = '" & Request.Form("StudentName") & "' AND ClassID = '" & Request.Form("ClassID") & "' AND EvalDate = '" & Request.Form("EvalDate") &"'"
rsgetevalid.open strSQLgetevalid, conn

%>
<form name="email" action="email.asp" method="post">
<p align="center">Evaluation added successfully.
<input type="hidden" name="evalid" value="<% = rsgetevalid("EvalID") %>" />
<input type="hidden" name="action" value="validate_login" />
<input type="hidden" name="username" value="<% = username %>" />
<input type="hidden" name="password" value="<% = password %>" />
<input type="submit" VALUE="E-mail Evaluation" />
</p></form>

<%
rsgetevalid.Close
Set rsgetevalid = Nothing
end if
%>
<!--#INCLUDE FILE = "footer.asp" -->
 
Sort by date Sort by votes
The quick answer is, double up the single-quotes. For example:
Replace(Request.Form("StudentName"), "'", "''")

But the better answer is, you really need to look into preventing sql injection, and sanitizing your data before allowing SQL to execute.
 
Upvote 0 Downvote
how do i prevent sql injection? ive looked around on the web and couldnt find anything relevant to what im working on.
 
Upvote 0 Downvote
Really? Google search of "sql injection classic asp" give me tons of stuff. Why not start with the first link:

Also, is it possible for Request.Form("A1A") to ever return a non-numeric value? If so, Int(Request.Form("A1A")) will give an error displaying some of your SQL code, giving the user some insight into your database structure, table names etc.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
261
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
240
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Problem: sql-Select with request.form
2
Replies
22
Views
392
leifoet
leifoet
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
658
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to update the content of radio buttons in a database?
Replies
2
Views
158
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top