Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2007-11-23
3
4,886 Views
Last Modified: 2013-11-26
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
Comment
Question by:ljhodgett
3 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20338402

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
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 250 total points
ID: 20338406
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
 
LVL 12

Assisted Solution

by:AGBrown
AGBrown earned 250 total points
ID: 20343153
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question