asked on

Update / Insert / Delete (Parameters)

Hello All;

Trying to wrap up this part of the SQL Server Conversion and Possibly secure the site
From possible SQL Injection.

OK.
What I need to know, is is there any special coding that needs to be done besides just adding what I have.
And I "THINK" that this needs to be added for every Column that is going to be:
Updated / Inserted / Deleted

SPOcmd.Parameters.Append SPOcmd.CreateParameter("@SPUsername", adVarChar,adParamInput, 25, strSUsername)

But, is there anything else that needs to be done to tighten these types of Statements up?

Thanks All;
Carrzkiss

<%
Set InMemocmd=Server.CreateObject("ADODB.Command")
InMemocmd.ActiveConnection=objConn
if trim(request.form("MWC")="Memo") then
MineID = Int(request.Form("id"))
strMyMemo = request.Form("MyMemo")
strMyMemo = trim(Replace(strMyMemo, vbCrLf, "<br />"))
strMyMemo = trim(Replace(strMyMemo, "'", "''"))
strMemoTitle = trim(request.Form("MyMemoTitle"))
strMemoTitle = trim(Replace(strMemoTitle, "'", "''"))
MemoSQL = "INSERT INTO MyMemo (MID, MyMemoTitle, MyMemo) values ('" & MineID & "','" &strMemoTitle& "','" &strMyMemo& "')"
'response.Write MemoSQL & "<br / >"
'response.End()
InMemocmd.CommandText=MemoSQL
InMemocmd.Execute
%>

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Barron

ASKER

That is what I was thinking, but wanting to make sure before I did it.

Thank you Angel.

Carrzkiss

Wayne Barron

ASKER

Thank You Angel
Carrziss

Wayne Barron

ASKER

It seems that I have run into an issue that I cannot see to get around.

On this line

InMemocmd.Parameters.Append InMemocmd.CreateParameter("@strMemoTitle", adVarchar, adParamInput, 500, strMemoTitle )

I get this error
=====================================
ADODB.Command error '800a0d5d'
Application uses a value of the wrong type for the current operation.
/wl/Data/NewInserts.asp, line 65
=====================================

Wayne Barron

ASKER

that field is
varchar(MAX)

Wayne Barron

ASKER

I changed its values to 4000,
And that got rid of the error.

Now I have another error
>>Must declare the scalar variable

Going to work on that one now.

Carrzkiss

Wayne Barron

ASKER

Fixed it.

Had to change all Variables to a ?

MemoSQL = "INSERT INTO MyMemo (MID, MyMemoTitle, MyMemo) values (@MineID, @strMemoTitle , @strMyMemo )"

to

MemoSQL = "INSERT INTO MyMemo (MID, MyMemoTitle, MyMemo) values (?, ? , ? )"

That will do it.
Now I can complete the test and then implement the rest of them.

Have a good one Angel.
Carrzkiss

Wayne Barron

ASKER

Wanted to add in the actual working code.
(With some code correction from Angel's Code)

Hope this helps someone.
Thank You Angel.
Carrzkiss

<%
Set InMemocmd=Server.CreateObject("ADODB.Command")
InMemocmd.ActiveConnection=objConn
if trim(request.form("MWC")="Memo") then
MineID = Int(request.Form("id"))
strMyMemo = request.Form("MyMemo")
strMyMemo = trim(Replace(strMyMemo, vbCrLf, ""))
strMemoTitle = trim(request.Form("MyMemoTitle")) 
MemoSQL = "INSERT INTO MyMemo (MID, MyMemoTitle, MyMemo) values (?, ? , ? )"
InMemocmd.CommandText=MemoSQL
InMemocmd.Parameters.Append InMemocmd.CreateParameter("@MineID", adInteger, adParamInput,, MineID )
InMemocmd.Parameters.Append InMemocmd.CreateParameter("@MyMemoTitle", adVarchar, adParamInput, 500, strMemoTitle )
InMemocmd.Parameters.Append InMemocmd.CreateParameter("@MyMemo", adVarchar, adParamInput, 500, strMyMemo )
InMemocmd.Execute
%>

Open in new window

Guy Hengel [angelIII / a3]

the ? vs @parameter depends on some connection options .. using oledb, you should be able to use @parameter. with odbc, it might be ? ...

Wayne Barron

ASKER

This is my Connection

<%
      Set ObjConn = CreateObject("ADODB.Connection")
      objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=machineName;Database=MyDatabase;User ID=myuserconnname;Password=********;"
      objConn.Open
%>

So, I should be able to use it, but does seem like I can?? (SQLOLEDB)

Guy Hengel [angelIII / a3]

what if you put:

InMemocmd.NamedParameters = true

Open in new window

Wayne Barron

ASKER

Still gives the same error
>>Must declare the scalar variable

Guy Hengel [angelIII / a3]

? :/

Wayne Barron

ASKER

I feel the same here :)
No problems.
It works with the ? so I am happy with that.

Do not worry about Angel.
Have an awesome day.

Carrzkiss