GAK!!! Converting to ADO.NET and failing miserably

[ppetree]

After my last post here revealed and educated me about SQL Injection issues I have been trying to convert my old style code to ado.net and I can't seem to get this right! I'm sure there are tons of errors in this sample (SO NO LAUGHING! - LOL)

Please help...

----------- code sample ----------
<%@ Language=VBScript %>
<%

'here we are getting the info from the login form, scan off apostrophes to prevent sql injection
If InStr(Request.Form("uid"),"'") Then
uid = Replace(Request.Form("uid"),"'"," ")
Else
uid = Request.Form("uid")
End If
If InStr(Request.Form("pwd"),"'") Then
pwd = Replace(Request.Form("pwd"),"'"," ")
Else
pwd = Request.Form("pwd")
End If

' The Query string with replaceable parameters
string strSQL = "Select * From @table Where id = @userid AND pass = @pwd"

' Set the table name we'll work with
string dataTableName = "admins"

' Create a connect string - this one MAY work!
string connectionString = "DSN=ws-admin.data"

'I know I need these but not sure how to cast them
'objConn = Server.CreateObject("ADODB.Connection")
'objConn.Open(connectionString)

' Create a connection to the database
' Confuses me because of the above statements
SqlConnection mySqlConnection = new SqlConnection(connectString)

' Create a command object, set the command string
SqlCommand mySqlCommand = mySqlConnection.CreateCommand()
mySqlCmd.CommandText = strSQL
mySqlCmd.CommandType = CommandType.Text;

' Set the parameter types
mySqlCmd.Parameters.Add("@table", SqlDbType.nChar, 20)
mySqlCmd.Parameters.Add("@userid", SqlDbType.nChar, 20)
mySqlCmd.Parameters.Add("@pwd", SqlDbType.Int)

' Replace the parameters with values
mySqlCmd.Parameters.Add("@table", "admin");
mySqlCmd.Parameters.Add("@userid", uid);
mySqlCmd.Parameters.Add("@pwd", pwd);

' Create the Data Adapter, set the command
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter()
mySqlDataAdapter.SelectCommand = mySqlCmd

' Create a dataset object to hold the results
DataSet myDataSet = new DataSet()

' Open the connection
mySqlConnection.Open()

' Get the query results into our local table
MySqlDataAdapter.Fill(myDataSet, dataTableName)

' See how many rows matched the admin_id and admin_pass
nFailed = myDataTable.Rows.Count

mySqlConnection.Close()

'if the user is found we will set the session okeydokey to TRUE allowing the user to gain entrance
If nFailed = 0 Then
'ooops if we got this far they dont know their login info or
'arent in the database so we send em back to try again
Response.Redirect "backtostart.asp"
Else
Session("admin") = True

'since the admin was found, we'll send them toodling on to the next page
Response.Redirect "whereadminsgo.asp"
End If

%>

--------- end code sample ---------

 

[jbenson001]

To avoid sql injection, create a stored procedure that uses parameters. I am not sure the way you are tying to do it will work.

 

[ppetree]

Can't do stored proceedures with access (access is all my isp supports) and this way will work I just haven't figured out how to make it work...

 

[jbenson001]

I'm sure you are right.. I've just never tried to do it this way since I use SQL Server .. I'll try and test and see if I can help at all

 

[ppetree]

There really aren't many differences between this way and a stored proceedure (atleast from what I could discern). Looks like all the connection stuff should be the same, building the string with the parameters is slightly different and not sure about getting the data back out.

I was just getting some errors that didn't make sense to me... and I've not had to write code in about 5 years so some of my perceptions and expectations are a bit skewed!

Not to mention I am WAY rusty! LOL

I do appreciate any help you can provide. I found most of this as examples in various places including a book I have on C# and some of this was taken from their ADO.net chapter. Other pieces came from the M$ site or some of the coding sites. Seems no one has a complete example.

Phil

 

[chrissie1]

Yes this is the correct way and it will work no need for SP's.

BTW no need to check for apostro.. thingies since that is what you use the parameters for and they will take care of that.

So what is the first error you get?

did you do this in a code behind?

no more need for the createobject.


Not sure about the connectionstring but that would be easily founf on the net.



Christiaan Baes
Belgium

"My old site" - Me

 

[ca8msm]

Can't do stored proceedures with access

Why not? They have been supported since the 2000 version as I recall.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ppetree]

Chrissie,

Thanks for your response. I googled the error and it led me to discovering that I had to call a different class (OdbcConnection) instead of the SqlConnection class I was calling. That knowledge drove further changes through the code and in the end I was right back where I started with the same error in the same spot on the same line... sigh...

The error is:
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/customer/admin/admincheck.asp, line 26

OdbcConnection myConnection = new OdbcConnection("DSN=ws-servianc.data2")
------------------------------------------------^

Here is the changed code:
=========

 ===================
<%@ Language=VBScript %>
<%

'here we are getting the info from the login form, scan off apostrophes to prevent sql injection
If InStr(Request.Form("uid"),"'") Then
  uid = Replace(Request.Form("uid"),"'"," ")
Else
  uid = Request.Form("uid")
End If
If InStr(Request.Form("pwd"),"'") Then
  pwd = Replace(Request.Form("pwd"),"'"," ")
Else
  pwd = Request.Form("pwd")
End If
 
' The Query string with replaceable parameters
string strSQL = "Select * From @table Where admin_id = @userid AND admin_pass=@pwd"

' Set the table name we'll work with
string dataTableName = "admin"

' Create a connect string - this one MAY work!
'string connectionString = "DSN=test_dsn"

' Create a connection to the database
OdbcConnection myConnection = new OdbcConnection("DSN=ws-servianc.data2")
myConnection.Open()

' Create a command object, set the command string
SqlCommand mySqlCommand = myConnection.CreateCommand()
mySqlCmd.CommandText = strSQL
mySqlCmd.CommandType = CommandType.Text;

' Set the parameter types
mySqlCmd.Parameters.Add("@table", SqlDbType.nChar, 20)
mySqlCmd.Parameters.Add("@userid", SqlDbType.nChar, 20)
mySqlCmd.Parameters.Add("@pwd", SqlDbType.Int)

' Replace the parameters with values
mySqlCmd.Parameters.Add("@table", "admin");
mySqlCmd.Parameters.Add("@userid", uid);
mySqlCmd.Parameters.Add("@pwd", pwd);

' Create the Data Adapter, set the command
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter()
mySqlDataAdapter.SelectCommand = mySqlCmd

' Create a dataset object to hold the results
DataSet myDataSet = new DataSet()

' Get the query results into our local table
MySqlDataAdapter.Fill(myDataSet, dataTableName)

' See how many rows matched the admin_id and admin_pass
nFailed = myDataTable.Rows.Count

myConnection.Close()

'if the user is found we will set the session okeydokey to TRUE allowing the user to gain entrance
If nFailed = 0 Then
  'ooops if we got this far they dont know their login info or 
  'arent in the database so we send em back to try again	
  Response.Redirect "admin.asp"
Else
  Session("admin") = True
	
  'since the admin was found, we'll send them toodling on to the next page
  Response.Redirect "admin2.asp"
End If

%>

=========

===================

 

[ca8msm]

/customer/admin/admincheck.asp

Is this a ASP or ASP.NET page as the above seems to suggest the former?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ppetree]

asp

 

[chrissie1]

uhm. You do know that this code doesn't work in asp? And you do know the difference between the two?

Christiaan Baes
Belgium

"My old site" - Me

 

[ppetree]

.net is going to be the oo version... but as much as I love a good mystery, don't keep me in suspense too long or I'll have to pause, go pee and start some pop-corn! <g>

Do we need to change a file extension to .aspx or perform some other magic to make this work?

Thanks,

Phil

 

[philhege]

The "magic" is that ASP.NET (.aspx pages with .aspx.vb code-behind pages, compiled .NET dll's) uses the .NET Framework to perform system I/O, data comm, etc. It is a COMPLETELY different animal from "old" ASP, and simply changing the file extension won't do it for you.

 

[AlexCuse]

Enjoy the popcorn!

Ignorance of certain subjects is a great part of wisdom

 

[ppetree]

since my isp is not a .net shop, only offers access via a dsn then what is the method for avoiding sql injection?

 

[SQLDenis]

I noticed you said GAK, maybe you need GAC (Global Assembly Cache)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[philhege]

No .NET, no GAC.

The usual old-fashioned method of avoiding injection in Access string SQL statements is to strip any semicolon from the form field values. This eliminates run-on statements.

Um, I would also issue a "naughty boy" page to anyone who tries it. Heh heh.

Of course, given your environment, this question is better asked in the "old" ASP forum.

 

[ppetree]

Ok, thanks all!