Member Avatar for chdboy

I have this code

Dim con = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Danial\documents\visual studio 2010\Projects\ESI_PF_Payroll_V1\ESI_PF_Payroll_V1\Pay.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
con.Open() 
cmd.CommandText = "SELECT Firstname,Lastname FROM Employee where Firstname = @Firstname and Lastname = @Lastname"
cmd.Parameters.Add(New SqlParameter("@Firstname", TextBox1.Text)) .Value = TextBox1.Text
cmd.Parameters.Add(New SqlParameter("@Lastname", Lname_txt.Text)) .Value = Lname_txt.Text
dr = cmd.ExecuteReader
While dr.Read
    Label4.Text = dr(0)
End While
con.Close()
cmd.Connection = con
adaptor.SelectCommand = cmd
con.Open()
adaptor.Fill(dataset, "O")
Dim count = dataset.Tables(0).Rows.Count
If count > 0 Then
    MessageBox.Show("This Employee Does Exist!")

It does not diffrenciate between
Firstname = some name and Firstname = somename
Lastname = Some Last Name and Lastname = somelastname

sometimes it wors correct like
Firstname = Danial
but sometimes I do like
Firstname = Daniall it also takes it as a yes
why is this happening?

Edited by Reverend Jim because: Fixed formatting
  • 2 Contributors
  • 5 Replies
  • 257 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by chdboy

Dani AI

Generated

Short version: this looks like a logic/deployment bug rather than SQL injection. As noted, parameterized commands are the right defence — SQL injection would require you to build SQL from raw user text. The odd match ("Daniall" treated as "Danial") is almost always caused by one of a few local problems, not by attackers.

Common things to check:

  • You're connecting to a different copy of the database than the one you inspect (Visual Studio + AttachDbFilename or the project's "Copy to Output Directory" can produce a runtime copy). Verify the exact database attached to the SQL Server instance.
  • Command/parameter reuse: if you overwrite CommandText without clearing parameters you can end up with a stale/duplicated parameter list. Use cmd.Parameters.Clear() or create a new SqlCommand for each distinct query.
  • Open DataReader vs adapter: don’t call an adapter.Fill on the same command/connection while a reader is still open — close/dispose the reader first.
  • Hidden characters/trailing spaces or input normalization (Trim(), Normalize) can make strings look different; compare the literal values you send.

Practical debug steps:

  • Output the parameter values before execution (Debug.WriteLine) or capture the statement with Extended Events/Profiler to see what actually reaches SQL Server.
  • Run the same WHERE query directly in Management Studio against the same database instance to confirm results.
  • Use a simple existence check and strong disposal patterns. Example pattern (use your actual connection string in place of myConnectionString):
Using cn As New SqlConnection(myConnectionString)
    Using cmd As New SqlCommand("SELECT COUNT(1) FROM Employee WHERE Firstname = @fn AND Lastname = @ln", cn)
        cmd.Parameters.Add("@fn", SqlDbType.NVarChar, 100).Value = TextBox1.Text.Trim()
        cmd.Parameters.Add("@ln", SqlDbType.NVarChar, 100).Value = Lname_txt.Text.Trim()
        cn.Open()
        Dim matches As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        ' matches > 0 means row exists
    End Using
End Using

Also check that your Windows login has permissions on the instance you target. After verifying the attached database, parameter usage, and that readers are closed, the unexpected match should be reproducible and fixable — again, this is a logic/deployment issue, not a classic SQL injection problem.

Recommended Answers

All 5 Replies

Member Avatar for Reverend Jim

First of all, you should let SQL Server manage the location of your database and use the following connection string

Dim con = New SqlConnection("Server=.\SQLEXPRESS;Database=Pay;Trusted_Connection=yes;")

assuming your database name is Pay. Then use

con.Open()
cmd.CommandText = "SELECT Firstname,Lastname FROM Employee where Firstname = @Firstname and Lastname = @Lastname"
cmd.Parameters.Add(New SqlParameter("@Firstname", TextBox1.Text)) .Value = TextBox1.Text
cmd.Parameters.Add(New SqlParameter("@Lastname", Lname_txt.Text)) .Value = Lname_txt.Text
dr = cmd.ExecuteReader

If dr.HasRows Then
    While dr.Read
        Label4.Text = dr(0)
    End While
    MessageBox.Show("This Employee Does Exist!")
Else
    MessageBox.Show("This Employee Does Not Exist!")

It is possible that both Danial and Daniall exist in the table. I tried your code here and it returns the expected results. One more note - you should also do dr.Close() when you are done reading.

And to answer that actual question - using the query as you have set it up (with parameters) is good protection against SQL injection.

Member Avatar for chdboy

Ok,I have only limited number of entries in my database so I know that only Danial Exists not Daniall,and thank you about the connection string correction.

Member Avatar for chdboy

With the connection string you suggested .
here is the code

Dim con = New SqlConnection("Server=.\SQLEXPRESS;Database=Pay;Trusted_Connection=yes;")
                con.Open()
                cmd = New SqlCommand
                cmd.Connection = con
                cmd.CommandText = "SELECT Firstname,Lastname FROM Employee where Firstname = @Firstname and Lastname = @Lastname"
                cmd.Parameters.Add(New SqlParameter("@Firstname", TextBox1.Text)).Value = TextBox1.Text
                cmd.Parameters.Add(New SqlParameter("@Lastname", Lname_txt.Text)).Value = Lname_txt.Text

                cmd.CommandText = "SELECT Companyname FROM Employee where Firstname = @Firstname"
                cmd.Parameters.Add(New SqlParameter("@Firstname", TextBox1.Text)).Value = TextBox1.Text
                dr = cmd.ExecuteReader
                If dr.HasRows Then
                    While dr.Read
                        Label4.Text = dr(0)
                    End While
                    MessageBox.Show("This Employee Does Exist!")
Else
Messagebox("This Employee Does not Exists!")

This is the error that comes up

cannot open database"Pay" requested by the login.The Login failed.Login failed for user'My PC Name'.
Member Avatar for Reverend Jim

I assumed that the database name was Pay based on the name of the actual file. I also assumed that because you had specified Integrated Security=True that you were validating based on your current login username. Are you using windows authentication or SQL server authentication?

Member Avatar for chdboy

Windows authentication

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.