0

I am trying to create a login form using Visual Basic and MS Access as my database source but I keep getting error as ')'expected. Please find my code below and help me solve this, because I have spent the entire day trying to find solution to this error.

sql =("SELECT Username,Password from tblLogin") 
  Where Username= '" & Trim(txtUsername.text) & "' 
    And Password='" & trim(txtPassword.text) & "')';
marekful
  • 14,986
  • 6
  • 37
  • 59
Debbie Adjin
  • 1
  • 1
  • 4
    You need to be aware that that code will break if a user has a username or password with an apostrophe in it, e.g. "O'Reilly". To prevent that, you need to use SQL parameters instead of putting the values in directly as strings. – Andrew Morton Feb 09 '21 at 16:16
  • You also need to be aware that if a user entering `O'Reilly` breaks your code, then a user entering `Robert'); DROP TABLE tblLogin;--` can have disastrous consequences if the query executes with sufficient permissions. This is a serious security issue, called a "SQL Injection vulnerability". Another security issue is that you should not be storing passwords in plain text, rather salting & hashing them and storing a hash value; use the same salt & hashing function on the user input to compare. Same hash, let them in; Different hash, wrong password was provided: actual passwords are never in DB. – Mathieu Guindon Feb 09 '21 at 16:31
  • You are mixing up VBA and SQL. You are using VBA to create a string containing SQL. So, all the SQL stuff must be inside a string. The `Where`, which is a SQL keyword, is not in a string. You must append ` & _` to the first line. `&` is concatenate string. `_` is line continuation. Then start with a `"` on the next line to put the `Where` inside a string again. There must be a line continuation `_` preceeded by a space at every line end. – Olivier Jacot-Descombes Feb 09 '21 at 17:28

3 Answers3

3

NEVER concatenate user inputs into any SQL statement, use proper parameters instead.

This means your SQL should look like this:

sql = "SELECT [UserName], [Password] FROM tblLogin WHERE [UserName]=? AND [Password]=?;"

Note that there is no need to track whether a column wants single quotes or not, which makes things much more robust, not to mention much more secure. Also the unbalanced parentheses issue becomes irrelevant.

The sql string should be used in some ADO Command or DAO (? ...not really familiar with Access) QueryDef object, as the command's CommandText or the querydef's definition.

Then you add Parameters to the ADO command and supply their values in the order they appear in the SQL command string, or set the named querydef parameters' respective Value accordingly.

Exactly how that's done depends on what type of Connection you're working with; this answer shows how you can use DAO QueryDefs in Access to do this, and this answer shows how you can use ADO to do the same with a Command and Parameter objects.

As a security note, I need to mention that storing password in plain text in a database is a very bad idea. Best practice would be to salt+hash the passwords, and only store the resulting hashes in the database; login is successful not when the user input matches the stored password, but when the salted user input produces the same hash value as the one stored for the claimed login: neither the code, the database, nor the developer actually needs to know anyone's passwords. This is important, because humans have this tendency to reuse passwords elsewhere, so if weak security isn't a problem for this particular application, it becomes a problem when a user decides to reuse their Facebook login for it, or to reuse their password for [other app whose security is actually important].

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 2
    Note I've created [this Q&A](https://stackoverflow.com/q/49509615/7296893) enumerating the possibilities of using parameters in Access. Unfortunately, there are many places you can use SQL in Access, and how to use parameters depends strongly on how you're using SQL (it's commonly neither ADO nor DAO and requires TempVars/form-based parameters). – Erik A Feb 09 '21 at 17:22
0

Try replacing your line with this one instead:

sql = "SELECT Username,Password from tblLogin Where Username= '" &

Trim(txtUsername.text) & "'" & " And Password= '" &

trim(txtPassword.text) & "'"
mcutrin
  • 134
  • 11
  • 1
    There is no `&=` in VBA. – Olivier Jacot-Descombes Feb 09 '21 at 17:19
  • well yeah that's right. I'm used to code in VB.NET and just wanted to put the code in different lines to enhance the reading experience. I will edit it right now. Thanks! – mcutrin Feb 09 '21 at 17:26
  • Thank you very much for you help. I have been able to clear the error now but when I click the login button after entering the username and password, nothing happens: – Debbie Adjin Feb 10 '21 at 16:03
  • This is the code: Dim Username As String = "" con.Open() Dim sql As String Dim i As Boolean sql = "SELECT [UserName], [Password] FROM tblLogin WHERE [UserName]=? AND [Password]=?;" Dim cmd = New OleDbCommand(sql, con) i = cmd.ExecuteNonQuery If (i = True) Then MsgBox("Login successful") frmMain.Show() Else MsgBox("Login failed") End If cmd = Nothing con.Close() – Debbie Adjin Feb 10 '21 at 16:04
  • 1
    Why `& "'" & " And Password= '" &` and not simply `& "' And Password= '" &`? And I also would replace single quotes by 2 single quotes `Replace(Trim(txtUsername.text), "'", "''")`. This makes is safer and eliminates this SQL injection problem without the use of parametrized queries. – Olivier Jacot-Descombes Feb 10 '21 at 16:08
  • @DebbieAdjin `Dim Username As String = ""` and parameterized constructors are VB.NET code, not VBA. Feel free to edit your post to change the language tag accordingly; VB.NET is much more recent than VBA, and much more powerful too. For example you get to use *string interpolation* instead of `&` concat operators, and a lot of legacy string-manipulating functions that live in the `Microsoft.VisualBasic` have a much more .NET-idiomatic equivalent directly in the `String` class. By tagging with VBA instead of VB.NET, you get solutions that work for VBA but aren't optimal for VB.NET. – Mathieu Guindon Feb 10 '21 at 16:14
0

Try with:

sql ="SELECT Username, [Password] from tblLogin " & _
    "Where Username = '" & Trim(txtUsername.Text) & "' " & _ 
    "And [Password] = '" & Trim(txtPassword.Text) & "'";

Password is a reserved word.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • The [documentation](https://support.microsoft.com/en-us/office/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2) doesn't say `Password` is a reserved word. – Andrew Morton Feb 09 '21 at 16:18
  • 2
    @AndrewMorton The [correct documentation](https://support.microsoft.com/en-us/office/sql-reserved-words-b899948b-0e1c-4b56-9622-a03f8f07cfc8#bmdaidxsqln_p) does. And yours too, at the end, under Access database engine reserved words. – Erik A Feb 09 '21 at 17:17
  • 1
    @ErikA Thank you for the correction. What a lovely comment they have in there: "Because using these two modes results in two slightly different sets of reserved words, a query that uses a reserved word might work in one mode and fail in another mode." It's a good reason to always quote identifiers. – Andrew Morton Feb 09 '21 at 18:18