[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

execute query on sql server in asp.net using vb.net 2005

Hi,

I am currently getting into asp.net. I have a form with 2 .net text boxes one being txtuser and the other being txtpassword. What I am trying to do is query the database to see if the password is correct. The sql statment I would use is:-

"SELECT * FROM Users where username = " & txtUsername.text

The connectionstring is contained within web.config called conn.

I would appreciate your help please.

Many Thanks
Lee
0
ljhodgett
Asked:
ljhodgett
2 Solutions
 
Pratima PharandeCommented:

Dim strConnectionString As String = "connetion string here"
    Dim cnn As New SqlConnection(strConnectionString)
 
    Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommandSELECT * FROM Users where username = '" & txtUsername.text &"' and Password = '" & txtpass.text &"'", cnn)
            cmd.CommandType = CommandType.Text
         Dim dacmd As New SqlClient.SqlDataAdapter(cmd)
        Dim dscmd As New DataSet
        dacmd.Fill(dscmd)
 if( dscmd.Tables(0).Rows.Count =0)
         --- this is not valid password
    

Open in new window

0
 
Ashish PatelCommented:
Use the below code and change it according to your needs.
Dim MainDbCn As SqlConnection
Dim DBCommand As New SqlCommand
Dim DBSqlReader As SqlDataReader
 
        MainDbCn = New SqlConnection("Your Connection String")
        MainDbCn.Open()
        DBCommand.Connection = MainDbCn 
        DBCommand.CommandText = "SELECT * FROM Users where username = '" & Replace(txtUsername.text, "'", "''") & "' And [Password] = '" & Replace(txtPassword.text,"'", "''") & "' "
        DBSqlReader = DBCommand.ExecuteReader()
        
If DBSqlReader.HasRows Then
    'Success'
Else
   'Login Failed'
End If

Open in new window

0
 
AGBrownCommented:
Hi Lee,

While the above methods will work fine, you would never want to use this method in a real web environment as it opens up an exceptionally serious security problem.

To use these code examples to first understand SqlConnection and SqlCommand objects is fine, but please then take the time to understand why "dynamically" creating SQL commands by concatenating SQL text with user input textboxes is a serious problem. The basic problem is that a malicious user finding this code can do everything from find the structure of your database to deleting data, tables and even databases, users etc in the wrong environment. This is known as a SQL Injection Attack, and is very simple to do with the above code running your queries.

The solution to this problem is to use either parameterised SQL strings, or stored procedures. For a few extra lines of code per parameter, you completely remove the SQL Injection possibility.

Rather than repeat the details here, I'll reference a few questions and discussions for you to read. If you have any further questions about the code you need to use, then please do ask.

Andy

reference:
http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

Question with example code for parameters in SQL strings:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_21795498.html

A few questions related to injection attacks:
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_21837311.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_21786916.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_22153847.html
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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