Solved

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

Posted on 2007-11-23
3
4,878 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
Comment Utility

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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now