CODEWITHSUNDEEP
sql-servervb.net
Jad
Jad

Reputation: 91

Invalid Column name while column is there

I am trying to create a login window with MDI. It is connected to a SQL Server table test. I have changed the datatypes and deleted and recreated the DB. I have 2 columns: usr and pwd of datatype nvarchar. 

Dim connetionString As String
Dim cnn As SqlConnection

connetionString = "Data Source=.;Initial Catalog=test;User ID=sa;Password=sasql"
cnn = New SqlConnection(connetionString)

Dim cmd As SqlCommand
Dim myreader As SqlDataReader
Dim query As String

query = "Select usr From users WHERE (usr =" + TextBox1.Text + " and pwd = " + TextBox2.Text + ")"
cmd = New SqlCommand(query, cnn)

cnn.Open()
myreader = cmd.ExecuteReader()

If myreader.Read() Then
Else
    MessageBox.Show("Incorrect username/password !", "LOGIN ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If

cnn.Close()

Thank you so much.

Upvotes: 2

Views: 947

Answers (2)

Jonathan Allen
Jonathan Allen

Reputation: 70307

If you are using Tortuga.Chain, the code would look like this:

Dim ds As New SqlServerDataSource(connetionString)

Dim user = ds.From("users", new With {.usr = TextBox1.Text, .pwd = TextBox2.Text}).ToString.Execute();

If user Is Not Nothing Then
Else
    MessageBox.Show("Incorrect username/password !", "LOGIN ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If

If you want to stick to raw ADO.NET code, you need to use a parameterized query.

Dim connetionString As String
Dim cnn As SqlConnection

connetionString = "Data Source=.;Initial Catalog=test;User ID=sa;Password=sasql"
cnn = New SqlConnection(connetionString)

Dim cmd As SqlCommand
Dim myreader As SqlDataReader
Dim query As String

query = "Select usr From users WHERE (usr = @user and pwd = @pwd )"
cmd = New SqlCommand(query, cnn)
cmd.Parameters.AddWithValue( "@usr", TextBox1.Text)
cmd.Parameters.AddWithValue( "@pwd", TextBox2.Text)

cnn.Open()
myreader = cmd.ExecuteReader()

If myreader.Read() Then
Else
    MessageBox.Show("Incorrect username/password !", "LOGIN ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If

cnn.Close()

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76508

Remove the spaces from the value of connectionString.

Your query compares textual data, which is invalid unless the user enters ' at the start and end of the textboxes. This would be syntactically correct:

"Select usr From users WHERE (usr ='" + TextBox1.Text + "' and pwd = '" + TextBox2.Text + "')"

but logically flawed, since the user might attempt to inject malicious SQL into your textboxes. Your application is extremely unsafe. You must protect it against SQL injection and you need to encrypt the password of the user as well.

Upvotes: 0

Related Questions