Using Parameterized Queries in ASP : solution to single quote problem

[sasi510]

Hi folks,

this sasi again. There always seems to be a issue with SQL queries when you use them in ASP. The best example is a single quote problem (for example: if the user inputs don't then the query breaks). Here is a way to form your SQL queries which holds good even when the user inputs a data with single quotes on it.

Lets first take a look at the non-parameterized queries
<%
'//////////////////// Get the form values ///////////////

txtbox = Request.form("text")
country = Request.form("text2")
total_num = Request.form("text3")
user_name = Session("Username")

'////////////////////// Set the data base connection ///////////////////////

set conn=server.CreateObject("ADODB.connection")
conn.ConnectionString="Provider=SQLOLEDB;Data Source= hostname, port number;Initial Catalog=Database Name;User Id=username;Password=password;"
conn.Open

'//////////////////////// End of database connection code ////////////////////////

'//////////////////// building the insert query ///////////////

insert_query="insert into Table_Name(username, msg, msg_num, country) values (" & "'" & user_name & "','" & txtbox & "','" & total_num & "','" & country &"')"
rs.Open insert_query,conn

'//////////////////////// End of query //////////////////////////////
%>

While you use the above query, if the user inputs in one of the text box as don't then u will see that the query breaks. To avaoid this we go in for parametrized queries. Here we go


<%
'/////////////////// Parametrized Query ////////////////////////////////////

insertSQL = "INSERT INTO Table_Name(username, msg, msg_num, country) "
insertSQL = insertSQL & "VALUES (?,?,?,?)"

Set courseCommand = Server.CreateObject("ADODB.Command")

courseCommand.ActiveConnection = conn
courseCommand.CommandText = insertSQL

courseCommand.Parameters(0).value = user_name
courseCommand.Parameters(1).value = txtbox
courseCommand.Parameters(2).value = total_num
courseCommand.Parameters(3).value = country

Set courseRS = courseCommand.Execute()

'/////////////////////////////// End of Query ////////////////////////////////////////////

%>

Hav fun guys,

-Sasi

[teeyo985]

that's the code i'm using under Visual studio 2008

you must import system.data.sqlcommand
dim connexion as new sqlconnection("server=serverName;database=database Name;user id=User_id;password=password")

dim cmd as new sqlcommand("insert into table_name values( @v1 , @v2 , @v3 )",connexion)
with cmd.parameters
.add("@v1",SqlDbType.Nchar,30).value = textbox1.text
.add("@v2",SqlDbType.Int,30).value = textbox2.text
.add("@v3",SqlDbType.Nchar,30).value = textbox3.text
end with
try
cmd.executeNonQuery
catch ex as exception
msgbox(ex.message)
end try

[sasi510]

Hi teeyo985,

I did not try it in visual studio, hence I think I missed that part. But I was able to run the above code in a Web Server having Microsoft IIS Version 6.0. Anyhow thanks for noticing that.

Cheers,
-Sasi