1

It's a simple piece of ASP code. I shouldn't be having problems with it. Yet, here it is!

I have searched through Stack Overflow and can't find an answer. I have an SQL insert that keeps creating two records. I can't find a reason, rhythm nor rhyme to resolve this issue. What is causing me to have this duplicate record?

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; DATA 

SOURCE=c:/inetpub/wwwroot/website/database/msg.mdb"
sql="INSERT INTO msg (uid,thisuid,bizid,ucomments,posted) VALUES 
('"&request("uid")&"','"&request("thisuid")& 
"','"&request("bizid")&"','"&request("ucomments")&"','"&Now&"');"

Set rs= Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 2
conn.Execute sql
conn.close

'response.redirect "show-msg.asp"
%>
Makyen
  • 31,849
  • 12
  • 86
  • 121
James Nelson
  • 61
  • 13
  • 1
    I feel this needs to be asked but why are you using an MSAccess database to power a web application? There is no excuse to be at least using SQL Server Express edition to power smaller websites, MSAccess was never designed to run in a web-based environment. You don't need to Open the `ADODB.Recordset` and call `Execute()` on the `ADODB.Connection` these both execute the `sql` hence the two records. – user692942 Jul 04 '19 at 07:34
  • 3
    Be careful with SQL injection here - this should not go onto the internet until that is fixed. – halfer Jul 04 '19 at 07:47
  • @halfer so true. That's how servers get compromised and become part of botnets. But we can't fix the world, I guess. :-( – Shadow The GPT Wizard Jul 04 '19 at 10:25
  • 2
    Ah, but we can try, @ShadowWizard `:=)` – halfer Jul 04 '19 at 10:41

1 Answers1

4

You open your SQL statement with rs.open and then you execute it with conn.execute -> double insert There's no need to create a recordset for an insert, the conn.execute is enough.

However, both methods rs.Open and conn.Execute are open to SQL Injection due to the way the SQL statement has been constructed. In this scenario, the best approach is to sanitise any input before passing directly into a SQL Statement and switch to using parameterised queries which use the ADODB.Command object.

user692942
  • 16,398
  • 7
  • 76
  • 175
  • The blatant [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) though is a problem, they should use [the `ADODB.Command` object with a `CommandType` of `adCmdText`](https://stackoverflow.com/a/20702205/692942) instead of `ADODB.Connection`'s `Execute()` method. – user692942 Jul 04 '19 at 07:44
  • Thank you Sir! I knew it was something small. Haven't work with an insert in a while. Thanks again! – James Nelson Jul 04 '19 at 07:47
  • @JamesNelson I strongly recommend you switch to using `ADODB.Command` instead of persisting with the current approach or it will come back to haunt you in the end. – user692942 Jul 04 '19 at 07:48
  • Haven't done this type of programing in a long time now. Over 12 years now. Ever since the Red Alert worm took out all my servers. During that time my main competitor snached up my domain name and cyber squted on it for 10 years. I finally have it back and have started developing again. A lot has changed since then. Be patient with me, I'm trying! I'll have to look into the ADOODB thing. I have seen references to it but, I don't know anything about it yet. Thanks for your help guys. I'm keeping a list of everyone that has helped along the way. – James Nelson Jul 04 '19 at 08:01
  • @JamesNelson don't worry the question stays around for the benefit of everyone, just want to make sure the right advice is being given for everyone's benefit. Keep at it, to be honest, you might want to look into new tech like ASP.Net using VB.Net if you are comfortable with VBScript the jump isn't too big and Classic ASP / VBScript are over 20+ years old now. – user692942 Jul 04 '19 at 08:18