MySQL question

[James Proctor]

Ok I have this code

std::string query = "SELECT * FROM account WHERE UserName = '" + mysqlname + "'";
mysql_query(conn, query.c_str());

It does a query on the database based on the users username but now how do I get the results?

What I want is to look up in the database to see if the username is already there. If it doesn't find the username then send an error message "wrong Username" or something like that. If it finds the username then I want it to compare the password for that username with the password entered.

I have the logic behind it all setup sort of. I have the password set in a config file and it compares the password from the config file with the password entered. Throws an error if the password is incorrect and sets bool auth to true if the password is correct. If auth is true then it allows the client to connect to the server and if it is false then the connection fails.

[Goilveig]

Few things:


1. The code you're looking for should be something like this:

Code: Select all

MYSQL_RES * result = mysql_store_result(conn);
int numRows = 0;
if (result)
     numRows = mysql_num_rows(result);

// If you need to access the actual data, you can use mysql_fetch_row()

mysql_free_result(result)

2. Be SURE you sanitize your DB inputs. For example, if I typed in a username of
Goilveig";DROP TABLE account;--
you'd lose your entire user database. Any input from a remote client -- even input supposedly being generated by the client without the user's input -- is suspect.

3. Don't give separate "incorrect username" and "incorrect password" messages. Use one unified message for both errors. Why give a brute-force cracker the benefit of being able to tell if a username is valid? Allowing the cracker to separately validate usernames and passwords makes the job easier, and reduces your security.

[James Proctor]

Thanks for replying! It seems like that just attempts to connect to the database but doesn't do the query. results returns true no matter what I enter for my username.

My code:

std::string mysqlname = playerData.title;
std::string query = "SELECT * FROM account_data WHERE UserName = '" + mysqlname + "'";
mysql_query(conn, query.c_str());

MYSQL_RES * result = mysql_store_result(conn);
int numRows = 0;
if (!result)
{
numRows = mysql_num_rows(result);
std::cout << "Found your Username!";
}
else
{
std::cout << "No such user or incorrect password!";
}

[Goilveig]

Result will always be true (a non-null pointer) if:

* The query COULD have returned results (for example, if it's a select-like statement, rather than an insert-like one)
* Storing the results (if any) succeeded (this can fail e.g. if you run out of memory and can't allocate the result)

On a failed username, you should get a non-null (true) result, but mysql_num_rows will return 0 (meaning it was a successful query but with 0 results). Basically, a SELECT statement always gives a result, but the result might have 0 rows. An INSERT will never give a result, and you shouldn't call mysql_num_rows() because you're effectively passing in a NULL pointer in that case.

Your checking should look like this:

Code: Select all

MYSQL_RES * result = mysql_store_result(conn);
int numRows = 0;
if (result)
{
     numRows = mysql_num_rows(result);
}
if (numRows != 0) 
{
std::cout << "Found your Username!";
}
else
{
std::cout << "No such user or incorrect password!";
}