Hey folks - I’m working through some examples and I’m running into a problem. I have exhausted the online support docs on the subject. Any and all assistance is appreciated.
Situation: Running on Xojo cloud, have PostgreSQL database enabled and configured. I’m working on a small web app - function is irrelevant for this questions. It’s just a training exercise. I establish the database connection in a Session Open event, have a DB property is Session. On a modal window dialog, I have a Go button, a username field, and a password field. The go button action event has the code:
Dim ps As PostgreSQLPreparedStatement = Session.DB.Prepare(“SELECT * FROM users WHERE username = $1 AND password = $2”)
Dim rs As RecordSet = ps.SQLSelect ( usernameField.Text , passwordField.Text )
If rs = Nil Then
MsgBox ( "Error: " + Session.DB.ErrorMessage )
loginErrorLabel.Visible = TRUE
Return
Else
Self.Close
MainMenu.Show
End If
It compiles and runs fine, until I hit the go button. I then get a NilObjectException on “Dim ps As PostgreSQL…” line. I understand what the error is telling me, but I don’t understand why I’m getting it since I create ps in the line itself. (Hopefully my conceptual grasp of this is correct - I’m pretty knew to this language). Additionally, I took this code from the Xojo website, from an example for submitting prepared SQL statements. Any suggestions or thoughts on what I am missing?
Where and how do you initialize the Session.DB property? It sounds like that’s what’s Nil.
Also, a couple of suggestions: Don’t keep a single instance of your database. Create an instance when you need it, then discard it when done. Why? If 1000 sessions are suddenly created, your app will attempt to create 1000 connections to the database, and it probably can’t.
More importantly, do NOT store passwords in your database. Passwords should be salted and hashed, not stored directly. You can use Crypto.PBKDF2 or my Blowfish project that includes Bcrypt for this purpose. For extra security, look into Security Through Obesity as a scheme.
[quote=294531:@Kem Tekinay]Where and how do you initialize the Session.DB property? It sounds like that’s what’s Nil.
Also, a couple of suggestions: Don’t keep a single instance of your database. Create an instance when you need it, then discard it when done. Why? If 1000 sessions are suddenly created, your app will attempt to create 1000 connections to the database, and it probably can’t.
More importantly, do NOT store passwords in your database. Passwords should be salted and hashed, not stored directly. You can use Crypto.PBKDF2 or my Blowfish project that includes Bcrypt for this purpose. For extra security, look into Security Through Obesity as a scheme.[/quote]
Some of this is available via https://github.com/thommcgrath/AuthenticationKit
In my session open event, I have the initialization of the database.
Dim DB as New PostgreSQLDatabase
DB.Host = “localhost”
DB.Port = 5432
DB.DatabaseName = “database_name”
DB.UserName = “user_name”
DB.Password = “password”
I had read an entry on the Xojo site that said to put it in the Session so you didn’t have to connect to it each time you wanted to touch it - so that as long as the session was active, the connection was there. At least, that was my understanding…I’m starting to get the feeling that is not the case!
So if I understand Kem correctly, my go button action code should be something like:
Dim DB as New PostgreSQLDatabase
DB.Host = “localhost”
DB.Port = 5432
DB.DatabaseName = “database_name”
DB.UserName = “user_name”
DB.Password = “password”
Dim ps As PostgreSQLPreparedStatement = DB.Prepare(“SELECT * FROM users WHERE username = $1 AND password = $2”)
Dim rs As RecordSet = ps.SQLSelect ( usernameField.Text , passwordField.Text )
If rs = Nil Then
MsgBox ( "Error: " + DB.ErrorMessage )
loginErrorLabel.Visible = TRUE
Return
Else
Self.Close
MainMenu.Show
End If
Is that correct?
That seems like a TON of replication to create DB every time I need it.
As for the password hash stuff, this is baby steps for me…I’ll get to the resources you suggested!
Baby steps are good. Get it working in Session and then think about Kem’s suggestion. One way to implement it without replicating a lot of code would be to create a method that returns a database object.
Function NewDatabaseConnection() as PostgreSQLDatabase
dim DB as New PostgreSQLDatabase // this is ok, because we're going to return it.
DB.Host = "localhost"
DB.Port = 5432
DB.DatabaseName = "database_name"
DB.UserName = "user_name"
DB.Password = "password"
if DB.Connect then
return DB
else
// raise an exception? return nil? you choose
return Nil
end
end
Then in your code put
Dim DB as PostgreSQLDatabase = NewDatabaseConnection
Dim ps As PostgreSQLPreparedStatement = DB.Prepare...
etc.
Wayne nailed your problem. I suggest moving your DB connection code to a function like App.GetDB () As Database so in your other code, like the block you posted above, you can just do this:
dim db as Database = App.GetDB
When the local db variable goes out of scope, that connection closes.
[quote=294540:@Kem Tekinay]Wayne nailed your problem. I suggest moving your DB connection code to a function like App.GetDB () As Database so in your other code, like the block you posted above, you can just do this:
dim db as Database = App.GetDB
When the local db variable goes out of scope, that connection closes.[/quote]
