SQL to search for encrypted values not working

Question

I am using a button to trigger a SELECT statement, and based off of the criteria the user enters in 2 text boxes (SearchFirstTxt, SearchLastTxt), I send the Text values of those text boxes through an encryption class to find their match I return them in a SqlDataAdapter and use it to fill a DataTable. I then use DataGridView.DataSoruce = dt to add it to the DGV.

My question: If the user leaves both text boxes blank and clicks the "SearchBtn", it doesn't select all of the records. It actually only selects records with the same encrypted values.

Here is the code:

    eFirst = clsEncrypt.EncryptData(SearchFirstTxt.Text.Trim.ToUpper)
    eLast = clsEncrypt.EncryptData(SearchLastTxt.Text.Trim.ToUpper)

    conn.Open()
    cmd.Connection = conn

    If SearchFirstTxt.Text = "" Then
        cmd.CommandText = "Select * FROM Participant Where LAST_NM_TXT = '" & eLast & "' ; "
    ElseIf SearchLastTxt.Text = "" Then
        cmd.CommandText = "Select * FROM Participant WHERE FIRST_NM_TXT = '" & eFirst & "' ; "
    Else
        cmd.CommandText = "SELECT * FROM PARTICIPANT;"
    End If

    Dim adapter As New SqlDataAdapter(cmd)
    adapter.Fill(dt)

    DataGridView1.DataSource = dt

    Try
        For i As Integer = 0 To dt.Rows.Count - 1
            dt.Rows(i)("FIRST_NM_TXT") = clsEncrypt.DecryptData(eFirst)
            dt.Rows(i)("LAST_NM_TXT") = clsEncrypt.DecryptData(eLast)
        Next
    Catch ex As Exception
        MessageBox.Show("Error")
    Finally
        conn.Close()
    End Try

How can I select ALL of the records from the Participant dbo?

The result set looks like this if the text boxes are left blank: enter image description here

enter image description here

Edit: I switched my code, and it retrieves ALL of the results, however, now I am having difficulty returning them. (They return as encrypted, but not decrypted)

Here are the changes:

        If SearchFirstTxt.Text = "" And SearchLastTxt.Text = "" Then
        cmd.CommandText = "SELECT * FROM PARTICIPANT;"
    ElseIf SearchLastTxt.Text = "" Then
        cmd.CommandText = "Select * FROM Participant WHERE FIRST_NM_TXT = '" & eFirst & "' ; "
    ElseIf SearchFirstTxt.Text = "" Then
        cmd.CommandText = "Select * FROM Participant Where LAST_NM_TXT = '" & eLast & "' ; "
    End If

Print out the empty value after encription. Let see what it has. I suppose an empty string is encripted to some "xxx" value. — Thrash Bean, Feb 10 '14 at 16:26
It is not clear. If the textboxes are empty you want to retrieve every record from the Partecipant table? — Steve, Feb 10 '14 at 16:46
Yes, @Steve. That is correct. I will update my OP to reflect results. Thrash Bean, I am actually thinking the same thing, now that you mention it. — Mark C., Feb 10 '14 at 18:07
But if you want to retrieve all records if the textboxes are empty, why do you put a WHERE for this case? The WHERE should be applied in case you have an input (on the last else if) — Steve, Feb 10 '14 at 18:23
The last case is if both TextBoxes are empty, and there is no WHERE clause in the SQL, it is just "SELECT * FROM Participant;" — Mark C., Feb 10 '14 at 18:39

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

If I understand your requirements correctly, you need to add a check for the other search text when you try to search your encrypted data

If SearchFirstTxt.Text = "" AndAlso SearchLastTxt.Text <> "" Then
    ' Search the last only if you have a last and not a first'
    cmd.CommandText = "Select * FROM Participant Where LAST_NM_TXT = @searchLast"
    cmd.Parameters.AddWithValue("@searchLast", eLast)

ElseIf SearchLastTxt.Text = "" AndAlso SearchFirstTxt.Text <> "" Then
    ' Search the first only if you have a first and not a last'
    cmd.CommandText = "Select * FROM Participant WHERE FIRST_NM_TXT = @searchFirst"
    cmd.Parameters.AddWithValue("@searchFirst", eFirst)

ElseIf SearchFirstTxt.Text = "" AndAlso SearchLastText.Text = "" Then
    ' Both emtpy so search everything'
    cmd.CommandText = "SELECT * FROM PARTICIPANT;"

Else
    ' Both filled so search exactly (not sure if this is needed)'
    cmd.CommandText = "Select * FROM Participant " & _
                      "WHERE FIRST_NM_TXT = @searchFirst " & _
                      "OR LAST_NM_TXT = @searchLast"
    cmd.Parameters.AddWithValue("@searchFirst", eFirst)
    cmd.Parameters.AddWithValue("@searchLast", eLast)
End If

Dim adapter As New SqlDataAdapter(cmd)
adapter.Fill(dt)

Notice that I have removed your string concatenation and used a parameterized query. It is safer (avoids Sql Injection) and remove parsing problems (what if your encrypted text contains a single quote?)

Supposing then that you want to show the decrypted data you apply your deCryptData function to the values in the datatable, not to the same values used for the search (you already know the clear text)

Try
    For i As Integer = 0 To dt.Rows.Count - 1
        dt.Rows(i)("FIRST_NM_TXT") = clsEncrypt.DecryptData(dt.Rows(i)("FIRST_NM_TXT").ToString)
        dt.Rows(i)("LAST_NM_TXT") = clsEncrypt.DecryptData(dt.Rows(i)("LAST_NM_TXT").ToString)
    Next
Catch ex As Exception
    MessageBox.Show("Error")
Finally
    conn.Close()
End Try

edited May 23 '17 at 10:33

Community

1
1

answered Feb 10 '14 at 19:17

Steve

213,761
22
232
286

Thanks, @Steve. Great answer - however - can you elaborate on what searchFirst , searchLast refer to? Are they 'made up'? I know the Parameters help against Sql Injection. I will also have to research the 'AndAlso' vs. 'And' .. As for the second problem with this solution, please see my edit in the OP. Any idea on how to decrypt? – Mark C. Feb 10 '14 at 19:22
The `@searchFirst` and `@searchLast` are just the parameters name. They will be filled with the real value for them when the parameter is added to the parameter collection – Steve Feb 10 '14 at 19:32
Works great - thank you, sincerely. I have one more question for you, if you don't mind. Should I be replacing the SearchFirstTxt.Text and SearchLastTxt.Text with eFirst and eLast? – Mark C. Feb 10 '14 at 19:37
1

Right, you should replace the unecrypted textbox with its encrypted counterpart. Fixing the answer – Steve Feb 10 '14 at 19:42
Steve, what are your thoughts on using .IsNUllOrEmpty vs "" ? – Mark C. Feb 10 '14 at 19:49
Not really necessary, the Text property is never null, then perhaps use IsNullOrWhiteSpace – Steve Feb 10 '14 at 20:13
Great - thanks for the answer and for the knowledge! I'll have to research more about the Paramters and exactly what they do. I know you mentioned the user adding a single quote, and what would happen in that scenario is now prevented. If you were going to populate a DGV based on a users' search, is this how you would do it? – Mark C. Feb 10 '14 at 20:17
Yes assign a datatable or a BindingSource is the accepted way to assign data to a DGV – Steve Feb 10 '14 at 20:19
Last question, as to not continue distracting you. Have you ever seen `DGV1.DataSource = queryName.ToList` ? – Mark C. Feb 10 '14 at 20:21
1

It is possible. if queryname is an IEnumerable and ToList materialize that in a list that implements the required binding interfaces – Steve Feb 10 '14 at 20:22

SQL to search for encrypted values not working

1 Answers1