Sounds weird but hear me out! I recently had to authenticate to a users table from a desktop app. The users table is used by a Rails app and uses Devise for authentication. This posed two issues 1) how do I hash my password properly 2) connect to database with out use of AR-JDBC.
First thing is to get a connection going. No need to use active-record-jdbc-adaptor, this is not a Rails app. You just need to require the libraries to make the connection work. I use jdbc-jdts, which is used by active-record-jdbc-adaptor. To setup your connection:
Jdbc::JTDS.load_driver(:require) if defined?(::Jdbc::JTDS.load_driver)
conn_str = “jdbc:jtds:sqlserver://220.127.116.11;instance=test;databaseName=test1″
connSelect = java.sql.DriverManager.get_connection(conn_str, “admin”, “secret”)
To run queries, do the following:
stmt = connSelect.create_statement
selectquery = “select * from users where email=’firstname.lastname@example.org'”
data = Hash.new
while (rsS.next) do
data[“first”] = rsS.getObject(“first_name”)
data[“last”] = rsS.getObject(“last_name”)
Note: To run an INSERT or UPDATE you’ll have to change use the stmt.execute_update() method. Another thing is when inserting into MSSQL, its good practice to escape your table and columns like so: INSERT INTO [customers] ([name], [user], [address]) VALUES (“John”, “email@example.com”, “123 ABC Ave”).
And thats it. To learn more, check out the active-record-jdbc-adaptor gem. Looking through the code helps you understand more.
Next, to authenticate, you’ll need to dive into the Devise gem. If you know anything about password encrypting, you’ll know that bcrypt is the defacto algorithm used for hashing passwords. To not make this sound so simple, the process actually does more than just encrypting plain text into a hash. Devise appends a pepper, a long random generated string which generated once and reused, to the plain text password. Next, Devise uses bcrypt, which prepends a salt in front of the password and encrypts the password, then stores into the database.
So you have something like this before the encryption happens:
To properly authenticate to the database without Devise, you’ll need to get the pepper string from your Rails app if you use one. It should be located in config/initializers/devise.rb and look for config.pepper. If the config.pepper line is commented out, then you don’t need the pepper. You will need the encrypted password from the database in order to decrypt properly. To decrypt and check for authentication, do the following:
password_with_pepper = plain_text_password + pepper
encrypted_password = BCrypt::Password.new(encrypted_password_from_db)
if encrypted_password == password_with_pepper
puts “Your passwords match!”
puts “Your passwords don’t match!”