SIMPLE Setting SQL variables using asp and a form

Question

My question is this: I am looking to insert values into my SQL database using variables. These variables are defined through asp and its related html form.

I am a beginner and here is the code I have so far.

My table:

<html>
<body>
<form action="trial3.asp" method="post">
Your first name: <input type="text" name="fname" size="20" /><br>
Your last name: <input type="text" name="lname" size="20" /><br>
Your email: <input type="text" name="email" size="20" /><br>
<input type="submit" value="Submit" />
</form>
</body>
</html>

My trial3.asp:

<%
'creating the inputs for the table, first name, last name, email
dim fname, lname, email
fname=Request.form("fname")
lname=Request.form("lname")
email=Request.form("email")
%>

MySQL code within the asp:

SQL="DECLARE @fname AS varchar(50), @lname VARCHAR(50), @email VARCHAR(100)"
SQL=SQL & "SET @fname='"+fname+"'"
SQL=SQL & "SET @lname='"+lname+"'"
SQL=SQL & "SET @email='"+email+"''"
SQL=SQL & "INSERT INTO Students (First_Name, Last_Name, Email) values (@fname, @lname, @email)"

Thanks for your help!

I'm not sure I can see an actual question there - the only point I could raise so far is that you don't need the variables in the insert statement, you can just insert the values directly. — Dave, Dec 13 '13 at 16:18
Hi Dave and thank you for your comment. Previously I replaced the @fname, etc. variables with strings and they were successfully inserted into my database. The variables that I enter into my form, however, are not entered into my database (nothing happens) and I don't know why. My question is how to I get my form to talk with my SQL code such that the code recognizes my forms inputs and is able to insert them as strings into my database, since this is not working? — user3100048, Dec 13 '13 at 16:39
One more thing. Your code has a security flaw that is very easy to hack. Read up on SQL Injection before you push any code like this to a production system or risk having your data compromised/vandalized. — JohnFx, Dec 17 '13 at 08:09
Use a Command object and pass parameters, see the [Bobby Tables site](http://bobby-tables.com/asp.html) for details. — webaware, Dec 18 '13 at 04:59

score 0 · Answer 1 · answered Dec 17 '13 at 08:07

0

He is trying to insert variables into the SQL string so as to avoid SQL injection through direct access (eg. the insert statement doesn't hold the input from the forms directly but rather through @fname, @lname)

I don't see a problem but I do see a lack of spacing between each SQL command so when you call the "&", they end up like this:

DECLARE @fname AS varchar(50), @lname VARCHAR(50), @email VARCHAR(100)SET @fname='test'SET @lname='ddddd'SET @email='Bob'

answered Dec 17 '13 at 08:07

Michael Lalonde Sudbury

1
1

It's not a guaranteed way to avoid having your database injected. His variables are `VARCHAR` so I'll just POST a malicious bit of HTML into them to fire a JavaScript. It all depends on how the data will be used, is this guy going to build a Student Register for example which will expose any malicious values that have been passed in. – user692942 Dec 20 '13 at 11:38

John · Answer 2 · 2013-12-13T17:34:35.233

What database are you using - SQL Server, MySQL, Access, or something else? Your query looks as if it's SQL Server specific.

Anyway, I don't think it needs to be as complex as that

SQL="INSERT INTO Students (First_Name, Last_Name, Email) values ('"& fname &"', '"& lname &"', '"& email &"')"

should do what you want

(NB - you can use either + or & to concatenate strings in Classic ASP.)

Edit - I've just read the comments which say more or less the same thing. If your query isn't working I recommend adding

Response.write SQL

on the line immediately before the line where you try to execute the query. It's really helpful in debugging. Classic ASP syntax makes it very easy to put a single quote or a comma in the wrong place

Stuart · Answer 3 · 2013-12-16T04:12:06.440

Please put the code below in your trial3.asp

<% Dim fname, lname, email, stringSQL, objectCon
   fname = Request.Form("fname")
   lname = Request.Form("lname")
   email = Request.Form("email")

   Set objectCon = Server.CreateObject("ADODB.Connection")
   objectCon.ConnectionString  "Driver={SQL SERVER};Server=server_name;UID=user_name;PWD=password;Database=database_name" 'SET CONNECTION STRING OF YOUR DATABASE
   stringSQL = "INSERT INTO Students (First_Name, Last_Name, Email) VALUES ('"& fname &"', '"& lname &"', '"& email &"')"

   objectCon.Open
   objectCon.Execute(stringSQL)
   objectCon.Close() %>

score -1 · Answer 4 · answered Dec 18 '13 at 16:44

-1

Found an answer.

Thank you all for your help.

The Response.write SQL was a lifesaver--turns out that I had one single quote too many and that my SQL spacing was all wonky. After I fixed this, the program ran well.

For SQL injection issues, am I covered if I use variables to insert information into my database? I was thinking that I can apply additional parameters to 'clean' the variables if need be too...

Any thoughts?

answered Dec 18 '13 at 16:44

user3100048

1
1

No your not covered your "variables" come from a HTML form so there is nothing stopping someone typing malicious code into your textboxes or crafting a HTTP POST direct to your `trial3.asp` page. – user692942 Dec 20 '13 at 11:32
Use a range protection script. There are questions on this site which have dealt with this - eg http://stackoverflow.com/questions/149848/classic-asp-sql-injection-protection. – John Dec 20 '13 at 12:34

SIMPLE Setting SQL variables using asp and a form

4 Answers4