How could I register a user using MySQL/Java?

[Tetrahydrocannabinol]

Hi guys, I've got a java program that uses my SMF database for username's and passwords. But the issue is they have to go onto my website and register.
How could I register them using MySQL, in my java app? I really only want to request the email, user, and pass, but if more info is needed for the database I can always add more fields.

[margarett]

I'm on the phone now so I can't really check the code but have a look at Sources/Register.php (that should use a Subs file) to understand the flow.
You basically need to add the record in smf_members and update some records in smf_settings (number of members and last member, IIRC)
Don't forget you need to hash the password

[Arantor]

Subs-Members.php, registerMember().

Doing it directly into the DB is a bad idea.

[Tetrahydrocannabinol]

Subs-Members.php, registerMember().

Doing it directly into the DB is a bad idea.

Do you have an alternate suggestion?
I couldn't really think of one since my app isn't directly connected to the site.

Edit:

When I send the sql in my java app, it doesn't seem to go through to the database. But if I copy that exact sql and put it in the database using phpmyadmin, it works. I'm confused as to why it's doing this.

[Arantor]

Wait, you're connecting to the database DIRECTLY from your app? That's a colossal security issue having the DB server directly exposed to the outside world.

[Kindred]

holy mackerel...    I can only echo Arantor's disbelief on this...

What you are doing is bypassing ALL security of SMF *AND* the basic security of your site.

[live627]

Use API calls.

[Tetrahydrocannabinol]

Wait, you're connecting to the database DIRECTLY from your app? That's a colossal security issue having the DB server directly exposed to the outside world.

Sorry for the late reply.

Yes, I have to.. And only the server connects to the database. Not the client that the end user sees/has.
I need MySQL to send information from the server to the site so the site works correctly..
The client sends the user, pass, etc, in a packet to the server, and the server then does whatever is needed, sending data back to the client if needed.

[Arantor]

So why can't the server portion be written in PHP, which can bridge to SMF normally?

[Tetrahydrocannabinol]

So why can't the server portion be written in PHP, which can bridge to SMF normally?

I don't know how I'd bridge my server to read the return php has.
And, how would I send it from the server to the php page?
I could have the server go to a specific page, but it'd have to be based on each user. And the only way I see that working is by using $_GET, which I'd rather not.. I need the page to be secure, I.E someone doesn't go to http://mysite.com/the_register_page.php/?data=fake_data and then register an account.

Also, not to mention, I don't know very much about php..

[Arantor]

So how does the server part you do have work?

Also, of course it has to be a specific page - just like it is in any web system ever built; you don't go to a unique page, though, you pass along an auth token (like the cookies) to identify the user. Just look at what SMF does

[Tetrahydrocannabinol]

So how does the server part you do have work?

Also, of course it has to be a specific page - just like it is in any web system ever built; you don't go to a unique page, though, you pass along an auth token (like the cookies) to identify the user. Just look at what SMF does

Right now, how my current system works (And you ain't gonna like it ) is the user logs into the client, the client sends their data to the server, the server (Already connected to my database) checks that the username exists, and if it doesn't, it returns an error to the user (This I'd rather have it say to register, hence the system I'm working on :3 ). But if it does exist, (Assuming the password was also right) it logs them in and gives them access to the app and game and etc..
As they play the server tracks stats and data and sends that to a different table in the database, for the highscores (and some other things).

[Arantor]

This doesn't actually answer my question at all... I didn't want generic details, I wanted specifics. I'm fluent in PHP and in SMF and I can usually muddle through reading Java too... the more details you can provide, the more chance someone will spend their limited free time being helpful.

[Tetrahydrocannabinol]

This doesn't actually answer my question at all... I didn't want generic details, I wanted specifics. I'm fluent in PHP and in SMF and I can usually muddle through reading Java too... the more details you can provide, the more chance someone will spend their limited free time being helpful.

Hmm. Well I don't really know exactly what you wanted to know, sorry..

Code (java) Select Expand

if (k == 0) {
socketStream.flushInputStream(inStream.buffer, 8);
inStream.currentOffset = 0;
aLong1215 = inStream.readQWord();
int ai[] = new int[4];
ai[0] = (int) (Math.random() * 99999999D);
ai[1] = (int) (Math.random() * 99999999D);
ai[2] = (int) (aLong1215 >> 32);
ai[3] = (int) aLong1215;
stream.currentOffset = 0;
stream.writeWordBigEndian(10);
stream.writeDWord(ai[0]);
stream.writeDWord(ai[1]);
stream.writeDWord(ai[2]);
stream.writeDWord(ai[3]);
stream.writeDWord(signlink.uid);
stream.writeString(username);
stream.writeString(email);
stream.writeString(password);
stream.writeString(getMacAddress());
stream.doKeys();
aStream_847.currentOffset = 0;
if (flag)
aStream_847.writeWordBigEndian(18);
else
aStream_847.writeWordBigEndian(16);
aStream_847.writeWordBigEndian(stream.currentOffset + 36 + 1
+ 1 + 2);
aStream_847.writeWordBigEndian(255);
aStream_847.writeWord(UpdateHandler.localClientVersion);// Client
// Version
aStream_847.writeWordBigEndian(lowMem ? 1 : 0);
for (int l1 = 0; l1 < 9; l1++)
aStream_847.writeDWord(expectedCRCs[l1]);

aStream_847.writeBytes(stream.buffer, stream.currentOffset, 0);
stream.encryption = new ISAACRandomGen(ai);
for (int j2 = 0; j2 < 4; j2++)
ai[j2] += 50;

encryption = new ISAACRandomGen(ai);
socketStream.queueBytes(aStream_847.currentOffset,
aStream_847.buffer);
k = socketStream.read();
}

I'm very sorry about the naming on the variables. The client wasn't built by me, and it's naming isn't near complete.

Basically, variable 'k' is the response code from the server.
I only showed you the statement for 0, because that's the state the client uses to login..
int[] ai is just random seeds used in the server (They don't have any primary function).
The signlink.uid is a unique id code so my server knows it's my client (This prevents other clients from connecting, partially).
The user, email, pass, and mac.. self explanatory.
The rest of that code is just various checks and etc the client needs..

And then on the server side:
(This is after the server had grabbed the data from the stream, i didn't think it was needed to show the basically same stream twice):

Code Select Expand

name = name.trim().toLowerCase();
pass = pass;

if (!name.matches("[A-Za-z0-9 ]+")) {
returnCode = 4;
}

if (name.length() > 12) {
returnCode = 8;
}

Client player = new Client(session, -1);
player.setPlayerName(name);
player.setPlayerName2(player.playerName);
if(email.length() > 0) {
player.setPlayerEmail(email);
player.setRegisterPlayer(true);
Misc.out("Register set to true");// Debugging...
}
player.setPlayerPass(pass);
player.setMacAddress(macAddress);
player.setInStreamDecryption(inC);
player.setOutStreamDecryption(outC);
player.outStream.packetEncryption = outC;
String IP = ((InetSocketAddress) session.getRemoteAddress())
.getAddress().getHostAddress();
player.saveCharacter = false;

if (name.length() < 2 || player.getPlayerPass().length() < 2)
returnCode = 3;

if (PlayerHandler.isPlayerOn(name)) {
returnCode = 5;
}

if (version != PlayerAssistant.getClientVersion()) {
returnCode = 6;
}

if (PlayerHandler.playerCount >= Config.MAX_PLAYERS) {
returnCode = 7;
}

if (player.getPA().checkBan(player.playerName, player) || Connection.isIpBanned(IP) ||
Connection.isMacBanned(macAddress)) {
returnCode = 4;
}
As you can see here, it checks some parameters before actually allowing the player in. Even though it's created the Client object, it's still not fully logged in because it still needs to verify the user doesn't exist.

So I load the player using this: (only if the returnCode from above isn't bad)

Code Select Expand

PlayerSave.loadGame(player, player.playerName, player.getPlayerEmail(), player.getPlayerPass());

Which checks both locally (where some data is stored) and in the SQL database if the player exists.

Code Select Expand

boolean newFile = false;
File charFile = new File(Config.CHAR_SAVE_LOC + p.playerName);
int forumLogin = ForumIntegration.login(playerName, playerEmail, playerPass, p.isRegisterPlayer());
if(forumLogin != 2)
return forumLogin;
if (!charFile.exists())
newFile = true;

if (newFile) {
p.setNewPlayer(true);
return 0;
}

if (!SaveMain.load(p, playerPass)) {
return 3;
}
Here I check if the forumLogin is not equal to 2, because 2 represents a successful login. if it's not 2, I return that so the player gets a message on why they can't get in (Since it seems to be an error related to the forum half). Then it checks if their file exists, so if it doesn't, it can create one.. Next is the local login which is just checking user and pass.

Now for the best part.. The code that handles all the connections to the forum database (Pretty sure this is mostly what you wanted..)

Code Select Expand

public static int login(String playerName, String playerEmail,
String playerPass, boolean register) {
if (!Config.forumInteg)
return 2;
if (!MysqlManager.connected()) {
boolean connect = MysqlManager.createConnection(Config.forumInteg);
if (!connect)
return 10;
}
if (register) {
Misc.out("Checking register");
return registerUser(playerName, "localhost", playerPass,
playerEmail);
}
String SHAPass = SHAGenerator.getSHA1(playerPass, playerName);
String sql = "SELECT * FROM " + Config.FORUMS_TABLE
+ " WHERE `member_name` = '" + playerName + "'";
try {
ResultSet r = MysqlManager.query(sql);
if (!r.isBeforeFirst()) {
return 12;
}
String pass = "";

while (r.next()) {
pass = r.getString("passwd");
}

if (SHAPass.equalsIgnoreCase(pass))
return 2;
else
return 3;
} catch (SQLException | NullPointerException e) {
return 8;
}
}
The first check using the boolean from Config is just for me as the programmer, for debugging and etc. The second checks if the database is connected, and if not it trys to (the boolean argument being whether it forces a new connection or not). If still no avail it returns 10, telling the user the login server is having some issues.
Then, as you can see, I register the user if they are set to register.. Under that I have the password being converted from raw text to the same SHA1 format that SMF uses and using that to check the database. If it doesn't match, it returns bad, if there was some form of an error, it returns bad, but if they are equal, it returns 2 (which means all good). 3 meaning bad pass.

And now for the part I'm stuck on (woo? :3 ). Note that a bunch of trace statements are left because, obviously it's not done :/

Code Select Expand

public static int registerUser(String username, String ip, String password,
String email) {
try {
String sql = "SELECT `email_address` FROM " + Config.FORUMS_TABLE;
ResultSet r = MysqlManager.query(sql);
if (!r.isBeforeFirst()) {
return 12;
}
String getEmail = "";

Misc.out("Searching users...");
while (r.next()) {
getEmail = r.getString("email_address");
if (getEmail.equalsIgnoreCase(email)) {
return 22;
}
}
r.close();
Misc.out("Done searching.");
} catch (SQLException | NullPointerException e) {
return 8;
}
String insert = "INSERT INTO `smf_members` (`member_name`, `date_registered`, `posts`, `id_group`,"
+ " `lngfile`, `last_login`, `real_name`, `instant_messages`, `unread_messages`, `new_pm`, `buddy_list`,"
+ " `pm_ignore_list`, `pm_prefs`, `mod_prefs`, `message_labels`, `passwd`, `openid_uri`, `email_address`,"
+ " `personal_text`, `gender`, `birthdate`, `website_title`, `website_url`, `location`,"
+ " `icq`, `aim`, `yim`, `msn`, `hide_email`, `show_online`, `time_format`, `signature`,"
+ " `time_offset`, `avatar`, `pm_email_notify`, `karma_bad`, `karma_good`, `usertitle`,"
+ " `notify_announcements`, `notify_regularity`, `notify_send_body`, `notify_types`,"
+ " `member_ip`, `member_ip2`, `secret_question`, `secret_answer`,"
+ " `id_theme`, `is_activated`, `validation_code`, `id_msg_last_visit`, `additional_groups`,"
+ " `smiley_set`, `id_post_group`, `total_time_logged_in`, `password_salt`, `ignore_boards`,"
+ " `warning`, `passwd_flood`, `pm_receive_from`) VALUES ('"
+ username
+ "', 'NOW()', '0',"
+ " '0', '', '0', '"
+ username
+ "', '0', '0', '0', '', '', '0', '', '',"
+ " '"
+ SHAGenerator.getSHA1(password, username)
+ "', '', '"
+ email
+ "', '', '0', '0001-01-01', '', '', '', '', '', '', '', '0', '1', '', '', '0', '', '1', '0',"
+ " '0', '', '1', '1', '0', '2', '"
+ ip
+ "', '"
+ ip
+ "', '', '', '0', '1', '',"
+ " '0', '', '', '4', '0', '"
+ getMD5(password).substring(0, 4) + "', '', '0', '', '1');";
Misc.out(insert);
Misc.out("Inserting to sql.");
if (MysqlManager.insert(insert) > 0) {
Misc.out("Returning.");
return 2;
} else {
return 8;
}
}
It's pretty simple.. It checks if the email exists, and if it doesn't, it inserts a new line.
The SQL string is broken up like that purely for readability. I didn't want 1 line 300 characters long xP.

What's odd is.. when the user registers.. It prints the sql and everything, but the MySQL database won't accept the new line.
Oddly enough, if I copy the line of SQL code it prints, and then go into phpMyAdmin and paste the code, it inserts a new line with the information.

[Arantor]

Actually what I wanted was to understand how you were connecting to the server as an endpoint (should have guessed you'd be connecting directly to a socket rather than say via something RESTful via HTTP), not how you were connecting to the database.

I would also wonder why you are calling .insert when presumably you should be calling .query?

[Tetrahydrocannabinol]

Actually what I wanted was to understand how you were connecting to the server as an endpoint (should have guessed you'd be connecting directly to a socket rather than say via something RESTful via HTTP), not how you were connecting to the database.

I would also wonder why you are calling .insert when presumably you should be calling .query?

Actually the insert method is something I made up,

Code Select Expand

public static int insert(String s) {
try {
return stm.executeUpdate(s);
} catch (SQLException | NullPointerException e) {
if (Config.SERVER_DEBUG)
Misc.out("Error getting results for database:" + s);
con = null;
stm = null;
}
return 0;
}
Reason being:

int executeUpdate(String sql)
                  throws SQLException
Parameters:
sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
Returns:
either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

See: Statement (Java Platform SE 7)

[Arantor]

Presumably Config.SERVER_DEBUG is truthy?

[Tetrahydrocannabinol]

Presumably Config.SERVER_DEBUG is truthy?

Yeah, it's set to true so I can check if the statement fails.

And it does.

Code Select Expand

Inserting to sql.
Error getting results for database:INSERT INTO `smf_members` (`member_name`, `date_registered`, `posts`, `id_group`, `lngfile`, `last_login`, `real_name`, `instant_messages`, `unread_messages`, `new_pm`, `buddy_list`, `pm_ignore_list`, `pm_prefs`, `mod_prefs`, `message_labels`, `passwd`, `openid_uri`, `email_address`, `personal_text`, `gender`, `birthdate`, `website_title`, `website_url`, `location`, `icq`, `aim`, `yim`, `msn`, `hide_email`, `show_online`, `time_format`, `signature`, `time_offset`, `avatar`, `pm_email_notify`, `karma_bad`, `karma_good`, `usertitle`, `notify_announcements`, `notify_regularity`, `notify_send_body`, `notify_types`, `member_ip`, `member_ip2`, `secret_question`, `secret_answer`, `id_theme`, `is_activated`, `validation_code`, `id_msg_last_visit`, `additional_groups`, `smiley_set`, `id_post_group`, `total_time_logged_in`, `password_salt`, `ignore_boards`, `warning`, `passwd_flood`, `pm_receive_from`) VALUES ('testaccount', 'NOW()', '0', '0', '', '0', 'testaccount', '0', '0', '0', '', '', '0', '', '', 'e6cb06c540796d95da54c76be21f0fdde438b71c', '', '[email protected]', '', '0', '0001-01-01', '', '', '', '', '', '', '', '0', '1', '', '', '0', '', '1', '0', '0', '', '1', '1', '0', '2', 'localhost', 'localhost', '', '', '0', '1', '', '0', '', '', '4', '0', '098f', '', '0', '', '1');


[Arantor]

So does MysqlManager have a method to get the last error message from MySQL?

[Tetrahydrocannabinol]

So does MysqlManager have a method to get the last error message from MySQL?

It doesn't. However, I can add an exception handler into the insert method. It should do the same.

Code Select Expand

Incorrect integer value: 'NOW()' for column 'date_registered' at row 1

Hm.. I had thought there was a NOW() function in sql or similar that'd set the date...

[Arantor]

NOW() returns a datetime, not an integer Unixish timestamp. You should use UNIX_TIMESTAMP() instead.

This still doesn't fix the other stuff that registerMember does, though... like updating the settings table with various things, but we'll get to that in a bit. I still really don't like the fact you're doing this like this because it also means you can't upgrade to SMF 2.1 without changing your server code.

[Kindred]

http://www.mysqltutorial.org/mysql-now/
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

http://stackoverflow.com/questions/14849911/difference-between-unix-timestamp-and-now-in-mysql
edit - ninja'd

[Tetrahydrocannabinol]

NOW() returns a datetime, not an integer Unixish timestamp. You should use UNIX_TIMESTAMP() instead.

This still doesn't fix the other stuff that registerMember does, though... like updating the settings table with various things, but we'll get to that in a bit. I still really don't like the fact you're doing this like this because it also means you can't upgrade to SMF 2.1 without changing your server code.

It's still giving the same error when using the UNIX_TIMESTAMP().
For now, I can just put a 0 as a placeholder so I can fix the rest of the system.. Then come back to this part..
As you said, there's other stuff to do.

Edit: I replaced the UNIX_TIMESTAMP() with (int) (new Date().getTime()/1000) and now the dates are registering correctly.

I went on my forums and checked the member list and the account was listed. it was also able to login to the forums..
What parts am I missing for the registration? I mean, besides making the forums auth the account via email, which I don't really need atm..

[Arantor]

It's still giving the same error when using the UNIX_TIMESTAMP().

Funny, it works perfectly when I do that. I also find it funny that the query worked correctly when you copy/pasted it because as was quite clear, NOW() didn't work, so I don't see why the query should have worked when you copy/pasted it before. They are MySQL functions, not Java ones.

What parts am I missing for the registration?

Updating the various stats, mostly.

besides making the forums auth the account via email

SMF does that out of the box.

[Tetrahydrocannabinol]

It's still giving the same error when using the UNIX_TIMESTAMP().

Funny, it works perfectly when I do that. I also find it funny that the query worked correctly when you copy/pasted it because as was quite clear, NOW() didn't work, so I don't see why the query should have worked when you copy/pasted it before. They are MySQL functions, not Java ones.

Yeah, that's what was confusing me. I wasn't understanding why they wouldn't work because of that.. But I guess Java's date class will have to do for now :3

Updating the various stats, mostly.

Such as the total members and etc?

SMF does that out of the box.

Yeah I believe that I made the accounts skip that part by changing a value or two in the sql I use.

[Arantor]

If the login process sees an email address, it tries to find a username for that email address internally and then tries to perform the login. The only reason that might not work is if you didn't set up the account properly (either unhashed password or no password salt) in which case conventional login is also messed up.

[Tetrahydrocannabinol]

If the login process sees an email address, it tries to find a username for that email address internally and then tries to perform the login. The only reason that might not work is if you didn't set up the account properly (either unhashed password or no password salt) in which case conventional login is also messed up.

I think I've got the correct password salt for pass, which from what I read was the username. And the server hashes the password before sending it to the database.

[Arantor]

If you have that done correctly (and I don't know, since I don't see the contents of getSHA1 listed), there is no reason why login via email wouldn't work properly. Just remember: when you log in with email/password, you do not pass the password in hashed the way you can for username.

[Tetrahydrocannabinol]

If you have that done correctly (and I don't know, since I don't see the contents of getSHA1 listed), there is no reason why login via email wouldn't work properly. Just remember: when you log in with email/password, you do not pass the password in hashed the way you can for username.

The SHAGenerator class wasn't made by me. but this is the getSHA1 method

Code Select Expand

public static String getSHA1(String passwordToHash, String salt) {
String generatedPassword = null;
try {
MessageDigest md = MessageDigest.getInstance("SHA-1");
md.update(salt.getBytes());
byte[] bytes = md.digest(passwordToHash.getBytes());
StringBuilder sb = new StringBuilder();
for (int i = 0; i < bytes.length; i++) {
sb.append(Integer.toString((bytes[i] & 0xff) + 0x100, 16)
.substring(1));
}
generatedPassword = sb.toString();
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
}
return generatedPassword;
}

And the login does work.. I believe.

[Arantor]

SMF works by taking the username, lower-casing it, concatenating the password and SHA1'ing the result, I could be wrong but I don't think the way you're injecting the salt into it is correct for that methodology.

[Tetrahydrocannabinol]

SMF works by taking the username, lower-casing it, concatenating the password and SHA1'ing the result, I could be wrong but I don't think the way you're injecting the salt into it is correct for that methodology.

Wouldn't SMF throw an error if a user created using that SQL tried to login? Because I made a test account and it logged in.

[Arantor]

Yes, it would - if it was incorrect. I'm only guessing because I don't know *that* much Java but it looked wrong to me is all.

[Tetrahydrocannabinol]

Yes, it would - if it was incorrect. I'm only guessing because I don't know *that* much Java but it looked wrong to me is all.

I don't know everything about Java either.. I got that class from a tutorial somewhere, so I can only hope it's correct.
I tried looking online for it for reference and can't seem to find it :L

Are other stats and etc that need to be updated, updated by SQL queries?

[Tetrahydrocannabinol]

I finished up the final touches of it today. I got the statistics updating and everything too.
:3