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

LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
<%
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 (@MineID, @strMemoTitle , @strMyMemo )"
InMemocmd.CommandText=MemoSQL
InMemocmd.Parameters.Append InMemocmd.CreateParameter("@MineID", adInteger, adParamInput,, MineID )
InMemocmd.Parameters.Append InMemocmd.CreateParameter("@strMemoTitle", adVarchar, adParamInput, 500, strMemoTitle )
InMemocmd.Parameters.Append InMemocmd.CreateParameter("@strMemo", adVarchar, adParamInput, 500, strMyMemo )
InMemocmd.Execute
%>

Open in new window

0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
That is what I was thinking, but wanting to make sure before I did it.

Thank you Angel.

Carrzkiss
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thank You Angel
Carrziss
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
=====================================
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
that field is
varchar(MAX)
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the ? vs @parameter  depends on some connection options .. using oledb, you should be able to use @parameter. with odbc, it might be ? ...
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what if you put:
InMemocmd.NamedParameters = true

Open in new window

0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Still gives the same error
>>Must declare the scalar variable
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
? :/
0
 
Wayne BarronAuthor, Web DeveloperAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.