Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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

0
Wayne Barron
Asked:
Wayne Barron
  • 10
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]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 Commented:
That is what I was thinking, but wanting to make sure before I did it.

Thank you Angel.

Carrzkiss
0
 
Wayne BarronAuthor Commented:
Thank You Angel
Carrziss
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Wayne BarronAuthor 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 Commented:
that field is
varchar(MAX)
0
 
Wayne BarronAuthor 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 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 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 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 Commented:
Still gives the same error
>>Must declare the scalar variable
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
? :/
0
 
Wayne BarronAuthor 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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now