Click here to Skip to main content
15,034,986 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
A client's website has stopped working on a Fasthosts (UK) hosted website. The core of the website is over 10 years old and is written in ASP. I am currently getting it converted to PHP for security and sanity reasons. I just need a quick fix until this is done.I am retrieving data from the database OK and writing new records.

I am using a form to enter the details:

<form method="POST" action="<%=MM_editAction%>" name="form1">
  <div align="center">
      <h2 style="text-align: center">Enter the New Vehicle's Details</h2>
      <table width="500" border="0" align="center" cellpadding="2" cellspacing="2">
      <tr bgcolor="#CCCCCC">
      <td bgcolor="#CCCCCC" class="tableleft">Marque</td>
      <td bgcolor="#FFFFFF" class="tableright"><input name="Marque" type="text" value="" size="32">


These details are then passed back to the same page using:

<input type="hidden" name="MM_insert" value="form1">
<td><input type="submit" value="Insert record"></td>


<%If (CStr(Request("MM_insert")) = "form1") Then...


Then the form contents are converted to variables and inserted using the following statement:

sql_insert = "INSERT INTO tblCarList ([Marque], [Category], [Model], [VYear], [Miles], [Description1], [Description2], [Description3], [Description4], [Description5], [Description6], [Description7], [Description8], [Description9], [Description10], [Description11], [Description12], [Description13], [Price], [Sold], [Pic1], [Pic2], [Pic3], [Pic4], [VActive]) VALUES ('" & Replace(iMarque,"'","''") & "', '" & iCategory & "', '" & Replace(iModel,"'","''") & "', '" & Replace(iVYear,"'","''") & "', '" & Replace(iMiles,"'","''") & "', '" & Replace(iDescription1,"'","''") & "', '" & Replace(iDescription2,"'","''") & "', '" & Replace(iDescription3,"'","''") & "', '" & Replace(iDescription4,"'","''") & "', '" & Replace(iDescription5,"'","''") & "', '" & Replace(iDescription6,"'","''") & "', '" & Replace(iDescription7,"'","''") & "', '" & Replace(iDescription8,"'","''") & "', '" & Replace(iDescription9,"'","''") & "', '" & Replace(iDescription10,"'","''") & "', '" & Replace(iDescription11,"'","''") & "', '" & Replace(iDescription12,"'","''") & "', '" & Replace(iDescription13,"'","''") & "', '" & Replace(iPrice,"'","''") & "', '" & Replace(iSold,"'","''") & "', '" & Replace(iPic1,"'","''") & "', '" & Replace(iPic2,"'","''") & "', '" & Replace(iPic3,"'","''") & "', '" & Replace(iPic4,"'","''") & "', '" & Replace(iVActive,"'","''") & "')"


This works fine but when I use the same page to update a record but change the SQL statement to:

sql_replace = "REPLACE INTO tblCarList ([Marque], [Category], [Model], [VYear], [Miles], [Description1], [Description2], [Description3], [Description4], [Description5], [Description6], [Description7], [Description8], [Description9], [Description10], [Description11], [Description12], [Description13], [Price], [Sold], [Pic1], [Pic2], [Pic3], [Pic4], [VActive]) VALUES ('" & Replace(iMarque,"'","''") & "', '" & iCategory & "', '" & Replace(iModel,"'","''") & "', '" & Replace(iVYear,"'","''") & "', '" & Replace(iMiles,"'","''") & "', '" & Replace(iDescription1,"'","''") & "', '" & Replace(iDescription2,"'","''") & "', '" & Replace(iDescription3,"'","''") & "', '" & Replace(iDescription4,"'","''") & "', '" & Replace(iDescription5,"'","''") & "', '" & Replace(iDescription6,"'","''") & "', '" & Replace(iDescription7,"'","''") & "', '" & Replace(iDescription8,"'","''") & "', '" & Replace(iDescription9,"'","''") & "', '" & Replace(iDescription10,"'","''") & "', '" & Replace(iDescription11,"'","''") & "', '" & Replace(iDescription12,"'","''") & "', '" & Replace(iDescription13,"'","''") & "', '" & Replace(iPrice,"'","''") & "', '" & Replace(iSold,"'","''") & "', '" & Replace(iPic1,"'","''") & "', '" & Replace(iPic2,"'","''") & "', '" & Replace(iPic3,"'","''") & "', '" & Replace(iPic4,"'","''") & "', '" & Replace(iVActive,"'","''") & "')"


I receive a Server 500 error.

The ID field is a Primary Key but the record is chosen using a passed URL variable:

<%
Dim thevehicle
Dim thevehicle_numRows
Dim tvSQLsource
Dim tvSQLid
Dim tvSQLstring

tvSQLsource = "SELECT * FROM tblCarList WHERE ID = "
tvSQLid = Request.Querystring("ID")
tvSQLstring = tvSQLsource & tvSQLid
Set thevehicle = Server.CreateObject("ADODB.Recordset")
thevehicle.ActiveConnection = MM_database_STRING
thevehicle.Source = tvSQLstring
thevehicle.CursorType = 0
thevehicle.CursorLocation = 2
thevehicle.LockType = 1
thevehicle.Open()

thevehicle_numRows = 0
%>


The database connection string is:

<!-- #include virtual = "/my_db/adovbs.inc" -->
<%
' Path to Databases
Dim MM_database_STRING, MM_database_STRINGPath, MM_database_STRINGPathBlank
MM_database_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("\my_db\car_database_new.mdb")
MM_database_STRINGPath = Server.MapPath("\my_db\car_database_new.mdb")
%>


Any help with this would be appreciated as I haven't kept up to date on coding for over 10 years.

What I have tried:

I have changed the form and variable names from the new record page when creating the update record page.
Posted
Updated 12-May-20 0:08am
v2

1 solution

There is no "REPLACE INTO" command in SQL. You're looking for UPDATE:
UPDATE (Transact-SQL) - SQL Server | Microsoft Docs[^]

But you have a much bigger problem: that code is vulnerable to SQL Injection:
Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]

If you're rewriting in PHP, you'll need to use prepared statements:
PHP: SQL Injection - Manual[^]
PHP: Prepared statements and stored procedures - Manual[^]


Edit: As Myche pointed out in the comments, REPLACE INTO does exist in MySQL:
MySQL :: MySQL 8.0 Reference Manual :: 13.2.9 REPLACE Statement[^]

It's not obvious which DBMS the code is connecting to. If it's MySQL, I suspect you'll need to pass in the primary key of the record you're trying to replace.
   
v2
Comments
Steve Blatch 11-May-20 12:16pm
   
Wow Richard, really appreciate your quick response. You are so right about the security issues which is way I'm asking a specialist to convert the site. Just need to get through the next few days! I'll give this a go and let you know how I get on!
MadMyche 11-May-20 12:39pm
   
I do believe that REPLACE INTO is MySql. And I do not miss all the MacroMedia code from the 90s
Richard Deeming 11-May-20 12:43pm
   
You're right:
MySQL :: MySQL 8.0 Reference Manual :: 13.2.9 REPLACE Statement[^]

That's not something I've seen before, and I can't say I'm keen on the syntax. It looks like it's closer to SQL Server's MERGE[^] than an UPDATE, but it's less obvious (to me) what it's doing.

I'd be surprised if the original code was using MySql, though. :)
MadMyche 11-May-20 13:06pm
   
That is classic MacroMedia Dreamweaver syntax with the MM_
Kinda reminds me of Hybris ImpEx statement: "INSERT_UPDATE"

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900