Sql commands in asp 3.0

[ccook]

Hello,

First off, im an intern not a fultime programmer...

Well anyhow, i wrote some code with the typical use of an sql string. The string has data from the user in it. I know this has a threat of sql injection. I was told to use sql commands. But, as far as i knew that was a .NET feature. Could someone tell me how this is done in 3.0?

'Current code
Strsql= "my sql statement +" & userdata & "'"
Set rs_admin=Conn.Execute(strsql)

 

[Chopstik]

Are you trying to do something like this?

strSQL = "SELECT * FROM SomeTable WHERE TableField = '" & UserData & "'"

If you could be somewhat more specific, might be able to help provide a better answer. Everything is absolute. Everything else is relative.

 

[ccook]

Right, sorry about that...

Heres some code...

<%
Dim Conn, dbname, dsnpath
Set Conn=Server.CreateObject(&quot;ADODB.Connection&quot
dsnpath= &quot;my pass/lgin&quot;
Conn.Mode = 3
Conn.Open dsnpath

Strsql= &quot;My sql string &quot;

Set rs_admin=Conn.Execute(strsql)
'Reset of code
%>

 

[ccook]

i want to do the same with sql commands. (the only way in .NET asp) (almost)

 

[Chopstik]

I think that you should be able to copy and paste that for ASP 3.0 without any problem. You might change this command:

Set rs_admin=Conn.Execute(strsql)

to this:

call Conn.Execute(strsql)

That way, you shouldn't have to deal with the rs_admin. Hope this is what you're looking for. Everything is absolute. Everything else is relative.

 

[ccook]

thank you A coworker showed me

I was looking for...

<%
Dim dbname, dsnpath
Set Conn=Server.CreateObject(&quot;ADODB.Connection&quot
dsnpath= &quot;Connection Stuff&quot;

Dim Cmd
Set Cmd = Server.CreateObject(&quot;ADODB.Command&quot
Dim RS
Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
Cmd.ActiveConnection = dsnpath
Cmd.CommandText = &quot;select * from tblAdmin&quot;
Cmd.Execute
RS.Open Cmd

Do While NOT RS.eof
Response.Write RS(&quot;AdminPassword&quot
Rs.MoveNext
Loop
'btw this is a test page, it wolnt print admin pass's
%>

 

[BG12424]

Can you please clarify your question and what you want to do? I don't understand your question.

There are multiple ways to get the same results. Are you wanting to get data from the database? If this is the case, then one way you can do this is open a recordset similar to:

dim strConn,strSql,rs
strConn = &quot;<< your database connection string >>&quot;

strSql = &quot;select * from <<table>> where <<field>> = &quot; & << your user value >>

On Error Resume Next

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open strSql, strConn

if not rs.bof and not rs.eof then
rs.movefirst
do while not rs.eof
response.write(rs(&quot;fieldname&quot.Value)
rs.movenext
loop
else
response.write(&quot;no records&quot
end if

if err.number <> 0 then
response.write(err.number & &quot; :: &quot; & err.description)
else
<< continue on >>
end if

regards,
Brian
AOL IM: FreelanceGaines

 

[Chopstik]

Actually, if you're looking to return a recordset, you don't really need the Command function, you can just open the recordset directly from the SQL string. Something akin to this:

<%
Dim dbname, dsnpath
Set Conn=Server.CreateObject(&quot;ADODB.Connection&quot
Conn.ConnectionString = <connection string here>
' dsnpath= &quot;Connection Stuff&quot;
Conn.Open

Dim RS
Set RS = Server.CreateObject(&quot;ADODB.Recordset&quot
RS.ActiveConnection = dsnpath
strsql = &quot;select * from tblAdmin&quot;
RS.Open strsql, Conn, 3, 1

Do While NOT RS.eof
Response.Write RS(&quot;AdminPassword&quot
Rs.MoveNext
Loop
'btw this is a test page, it wolnt print admin pass's
%>

This should give you the recordset without the added trouble of the command. HTH Everything is absolute. Everything else is relative.

 

[Chopstik]

Sorry, BGaines, just realised that I essentially repeated your post. My bad. :-( My apologies Everything is absolute. Everything else is relative.

 

[ccook]

Cool! like the code! But heres the story... (sorry i wasnt so clear) They want me to program the database access with a command object using parameters... for security. Im doing good so far... The code i first put in worked great.

Again thanks so much!

 

[BG12424]

That's cool, you can definitely do that too; however, in your code above you don't need to do both:

cmd.execute
<< and >>
rs.open cmd

I think that all you need is:
rs.open cmd

The cmd.execute is used when you don't expect a resultset back. regards,
Brian
AOL IM: FreelanceGaines