Is this a bad login algorithm in flash, php and mysql ?

Question

My site is in flash. The login is also in flash. This is the login flow:

1. Enter username and password

2. Send to authentication.php page

3. Here is my doubt on authentication.php page:

   (1) Check the post parameters, i.e username and password

   (2) Sha/hash the password

   (3) Q1(query 1), select username and password from users table

   (4) If matched, do another query(Q2), check if the user is still on ban status

   (5) If user is not in ban status, do a query (Q3) to check if the user is first log for today

   (6) If its first log, do update query (Q4) to rewards some money to the user

   (7) Create session, userid, a hashed session variable using md5. i.e

   $_SESSION['loggin'] = 1;

   $_SESSION['hash'] = md5(username.secret);

   (I use the $_SESSION['hash'] for another authentication in another page, just ignore this)

4. Return result to flash.

I know its a very bad login flow, alots of mysql queries, 2 hashing(php calculations). I am still thinking/looking for a better way for this. Like combining the query etc. Any ideas on how to improve the flow?? (I must meet all the stuff i mentioned above)

[The current login is slow]

Answer 1

The process itself isn't that bad.

It's really hard to tell why it's slow without performance data. There's a 90% chance performance is lost in a completely different place than you think. Put a couple of lines in the code which print the current time (including the milliseconds) to see where it hangs around.

A join could certainly help but if the time isn't spent there, it will just make the code harder to understand and maintain. If you find the DB access takes long, I suggest to check that you have the right indexes for your queries. If the indexes are OK, post a question with the DLL of the tables if you need help to write the join. But first, measure the performance.

Answer 2

Hi i would prefer to do an joins in one query.

SELECT u.user_id, u.name, u.password, u.last_last FROM users u 
LEFT JOIN banned b ON u.user_id = b.user_id
WHERE u.name = ? AND u.password = ?;

Answer 3

A few ideas regarding performance and security:

Steps 3 - 6: When reading from the database you should be able to select username, password, user status and last-login date in a single query, if your database is designed correctly (and as it seems ArneRie has just posted the pseudo-MySQL statement to do just that).

Updating the user (giving them reward) is a one-time INSERT/UPDATE and shouldn't effect performance that much.

Aside from cutting down on the number of MySQL queries (and optionally using salted hashes for passwords) the process looks ok.

Answer 4

To get a more abstract view of the problem the answers to the following questions would help:

• What environment are you developing in? 100% Locally (local code and Db or local code and external Db? etc)?
• Is this all 100% 'fresh' code, have you developed anything similar and not had these issues?

On a side note I use Chromes builtin resource monitor which shows the breakdown of a web pages size and time it took to download. I find this more useful than the one in FireFox/FireBug.

Have you tested each of your queries in something like PHPMyAdmin or similar to test query time? Add up the total time of each query and see what the difference is.

Good luck, Alex