homepage Welcome to WebmasterWorld Guest from 54.166.110.222
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
New SQL query, don't know where to put an "update" line
Can someone help?
dickbaker




msg:4159002
 7:16 am on Jun 25, 2010 (gmt 0)

I've had a page on my site that shows the information from the database fields based on ID. I also had a line that someone wrote for me that updated a field in the database ("pageviews") every time a visitor looked at the particular page.

The old code is wide open for SQL injection, though, so I have to change it. Problem is, I can't figure out how to get the new code to update the pageviews field.

Below is the old code with the update pageviews line, then the new code without it. If anyone could point me in the right direction to get the update line to work, I'd really appreciate it.

Old:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/myconnection.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("ID") <> "") Then
Recordset1__MMColParam = Request.QueryString("ID")
End If
dim theId
theID = Request.QueryString("ID")
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_myconnection_STRING
Recordset1.Source = "SELECT * FROM dbo.mydatabase WHERE ID = " + Replace(Recordset1__MMColParam, "'", "''") + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0

If (Recordset1.EOF or Recordset1.BOF) then
Response.Redirect("no_record_sorry.html")
Else
dim sql
sql = "Update dbo.mydatabase set pageviews =(" & Recordset1("pageviews") & " + 1) where id=" & theID


dim Cmd
set cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection = MM_myconnection_STRING
Cmd.CommandText = sql
Cmd.Execute()
Cmd.ActiveConnection.Close()
Set Cmd = Nothing
End If
%>


The new:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/myconnection.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("ID") <> "") Then
Recordset1__MMColParam = Request.QueryString("ID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_myconnection_STRING
Recordset1_cmd.CommandText = "SELECT * FROM dbo.mydatabase WHERE ID = ?"
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 5, 1, -1, Recordset1__MMColParam) ' adDouble

Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0

If (Recordset1.EOF or Recordset1.BOF) then
Response.Redirect("no_record_sorry.html")
End if
%>

 

topr8




msg:4159079
 9:47 am on Jun 25, 2010 (gmt 0)

imho, i think you should 'clean' the querystring ID ... eg build a function to check that it is the type of value you expect, this will also protect you from sql injection.
eg if ID should be an integer, check that it's an integer.

also i would rethink your query altogether.
i'd write it as a stored procedure and run both the query and the update at the same time (with appropriate logic) - this way you only make one database connection instead of 2, much more efficient and faster.

you'd need to write the stored procedure then you can call it is a similar way using the parameters

dickbaker




msg:4159417
 6:44 pm on Jun 25, 2010 (gmt 0)

You might as well be speaking Greek. ;)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved