First off I know that this is not “secure”… this is really just an internal notes app that switches views, so I don’t need a lesson on security, I’m just asking if anyone can help me with this code, or direct me to something that can help me learn how to do this…
I have a push button with this action code on it:
[code]if TextField1.Text = "user1" and TextField2.Text = "123" then
veiw1.show
self.close
elseif TextField1.Text = "user2" and TextField2.Text = "abc" then
view2.show
self.close
elseif TextField1.Text = "user3" and TextField2.Text = "xyz" then
view3.show
self.close
else
passworderror.Text = "Wrong Password! " // + mDb.ErrorMessage
//msgbox "Login Failed"
End if[/code]
This works fine, but I set up a table in my mysql database called listdb that has:
ID USER PASSWORD
1 user1 123
2 user2 abc
3 user3 xyz
I’m using:
SELECT * FROM listdb.users;
I can connect to the database fine and write single fields back and forth, I just can’t figure this part out…
I have the functionality that can add and delete the users and passwords from the database, but I’m wondering if someone can help me with the push button code to authenticate the user and password to switch views vs. manually adding the user in the push button code since obviously it make more sense to put these in the database rather than hard code into a push button…
What have you tried that hasn’t worked?
And secure or not you should at least have the respect for your users to hash their password.
Not a public app… just something I wrote for myself that lives on my desktop to keep my personal notes for different technical guides separated based on company and location… so really… username and password is misleading… really “user” would be company name… and “password” would just be the city…
"SELECT * FROM listdb.users WHERE user = ' + textfield1.text + "';" will only get the user record for the current user from the database. You can then compare textfield2.text with field(“password”).
I would use a prepared statement rather than the SQL injection technique above. I would also use the PASSWORD() function in mySQL to hash the actual password in the table.
My preferred code would be
[code]Dim ps As PreparedSQLStatement = db.Prepare(“SELECT * FROM listdb.users WHERE user = ? AND password = PASSWORD(?);”)
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0, TextField1.Text)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, TextField2.Text)
Dim rs As RecordSet = ps.SQLSelect()
If rs.EOF Then
MsgBox “Invalid Username or Password.”
Return
End If
Select Case TextField1.Text
Case “User1”
view1.show
Case “User2”
view2.show
End Select[/code]
Untested of course.
is it posible that code is correct but just doesn’t work with MySql?
Is it throwing an error or just always raising the messagebox? I work on Windows & don’t touch iOS which I’m guessing you are working due to the use of view. Does iOS have a messagebox class even? One thing that might cause issues is that the password is hashed in the prepared statement and is probably not in your table. Try replacing “PASSWORD(?)” with just “?” in the prepared statement.
this looks like it’s working… it compiles, i have code that tells me its connected… if i put in the wrong credentials, I get an error, if I put in the right credentials it throws an error here:
If rs.EOF Then
sorry… right credentials… gives the proper message box error… (not a real error)
THANK YOU! I’m getting farther… here is the current error though
Sub Action()
Dim ps As PreparedSQLStatement = mDB.Prepare(“SELECT * FROM listdb.user WHERE user = ? AND password = ?;”)
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0, TextField1.Text)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, TextField2.Text)
Dim rs As RecordSet = ps.SQLSelect()
[b] If rs.EOF Then <===== ERROR[/b]
MsgBox "Invalid Username or Password."
Return
End If
Select Case TextField1.Text
Case "master"
view1.show
Case "gear"
view2.show
End Select
I’m assuming the code stops running on the rs.EOF line. What is the exception?
Exception:NilObjectException
me: PushButton
ps: MySQLPreparedStatement
rs: Nil
self: Login.Login
also… im want to throw the error (if any) in a text field… so – passworderror.Text = "Wrong Password! " vs. using a message box
I think this is correct… but still throwing the exception at EOF…
Dim ps As PreparedSQLStatement = mDB.Prepare("SELECT * FROM listdb.user WHERE user = ? AND password = (?);")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0, TextField1.Text)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, TextField2.Text)
Dim rs As RecordSet = ps.SQLSelect()
If rs.EOF Then
passworderror.Text = "Wrong Password! "
Return
End If
Select Case TextField1.Text
Case "user1"
view1.show
Case "user1"
view2.show
End Select
can I throw this in under that statement?
if rs <> nil then
if rs.EOF then
msgbox “No records found.”
end
If rs is nil then there is a problem in the prepared statement. I think listdb.user should be listdb.users. Changing out the message box for writing to a text field should be fine.
YES!!!
Thank you… that was it. So for anyone else…
I have a window with two text fields, two labels and one push button.
Username and Password. MySql connection status, and and password error warning. Then a push button submit.
This is the code for the push button:
[code]Dim ps As PreparedSQLStatement = mDB.Prepare(“SELECT * FROM listdb.users WHERE user = ? AND password = ?;”)
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0, TextField1.Text)
ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, TextField2.Text)
Dim rs As RecordSet = ps.SQLSelect()
If rs.EOF Then
passworderror.Text = "Wrong Password! "
Return
End If
Select Case TextField1.Text
Case "user1"
view1.show
self.close
Case "user2"
view2.show
self.close
End Select[/code]
This is the Window Open event handler:javascript:BBCode.bold(“reply”);void(0)
[code]mDb = New MySQLCommunityServer
mDb.Host = "123.123.123.123" // whatever your database
mDb.Port = 3306
mDb.DatabaseName = "listdb"
mDb.Username = "username" // enter your database username
mDb.Password = "password" // enter your database password
If mDb.Connect Then
ConnectStatusLabel.Text = "Connected to server! " // + mDb.ErrorMessage
else
mIsConnected = False
ConnectStatusLabel.Text = "Error connecting server! " // + mDb.ErrorMessage
End If
App.AutoQuit = True[/code]
Method:
Method name: IsConnected
Return Type: Boolean
Scope: Public
[code] If mDB Is Nil Then
mIsConnected = False
End If
Return mIsConnected[/code]
Properties:
Name: mDB
Type: MySQLCommunityServer
Scope: Private
Name: mIsConnected
Type: Boolean
Scope: Private
I would still hash the password - mysql has the Password() function to do this which hashes your password into a CHAR(41) value. At the very least store the MD5 checksum rather than the actual password.
I also tend to add a 2nd table in my database & store the password separate from the user just to make it a little harder to find.