Mysql.h 0 results after query

Question

I made this:

int querystate;
std::string pol;
std::string login;
std::cout << "login: ";
std::cin >> login;
pol = "select * from table where login = '" + login + "';";
querystate = mysql_query(conn, pol.c_str());
if (querystate != 0)
{
    std::cout << mysql_error(conn);
}
res = mysql_store_result(conn);

while ((row = mysql_fetch_row(res)) != NULL)
{
    std::cout << row[0] << " " << row[1] << " " << row[2];
}

It is possible to make something like this?

if (res == 0)
{
    cout<<"there is 0 results";
}

I want to output text when query returns 0 results, for example:

there is no such login in the database.

Answer 1

First, your code is open to an SQL injection attack. You need to escape the login string using mysql_real_escape_string_quote(), eg:

std::string escapeStr(MYSQL *mysql, const std::string &str, char quoteChar)
{
    std::string out((str.size()*2)+1, '\0');
    unsigned long len = mysql_real_escape_string_quote(mysql, out.data(), str.c_str(), str.size(), quoteChar);
    out.resize(len);
    return out;
}

std::string pol = "select * from table where login = '" + escapeStr(conn, login, '\'') + "';";

Though, you really should be using a prepared statement instead, let MySQL handle the escaping for you.

Second, the mysql_query() documentation says:

To determine whether a statement returns a result set, call mysql_field_count(). See Section 5.4.23, “mysql_field_count()”.

Where the mysql_field_count() documentation says:

The normal use of this function is when mysql_store_result() returned NULL (and thus you have no result set pointer). In this case, you can call mysql_field_count() to determine whether mysql_store_result() should have produced a nonempty result. This enables the client program to take proper action without knowing whether the query was a SELECT (or SELECT-like) statement. The example shown here illustrates how this may be done.

See Section 3.6.8, “NULL mysql_store_result() Return After mysql_query() Success”.

And that last document says:

It is possible for mysql_store_result() to return NULL following a successful call to to the server using mysql_real_query() or mysql_query(). When this happens, it means one of the following conditions occurred:

• There was a malloc() failure (for example, if the result set was too large).

• The data could not be read (an error occurred on the connection).

• The query returned no data (for example, it was an INSERT, UPDATE, or DELETE).

You can always check whether the statement should have produced a nonempty result by calling mysql_field_count(). If mysql_field_count() returns zero, the result is empty and the last query was a statement that does not return values (for example, an INSERT or a DELETE). If mysql_field_count() returns a nonzero value, the statement should have produced a nonempty result. See the description of the mysql_field_count() function for an example.

So, for example:

std::string login;
std::cout << "login: ";
std::cin >> login;

std::string pol = "select * from table where login = '" + escapeStr(conn, login, '\'') + "';";
if (mysql_query(conn, pol.c_str()) != 0)
{
    std::cout << mysql_error(conn);
}
else if ((res = mysql_store_result(conn)) != NULL)
{
    while ((row = mysql_fetch_row(res)) != NULL)
    {
        std::cout << row[0] << " " << row[1] << " " << row[2];
    }
    mysql_free_result(res);
}
else if (mysql_field_count(conn) == 0)
{
    std::cout << "there are 0 results";
}
else
{
    std::cout << mysql_error(conn);
}

Alternatively, the documentation also says:

An alternative is to replace the mysql_field_count(&mysql) call with mysql_errno(&mysql). In this case, you are checking directly for an error from mysql_store_result() rather than inferring from the value of mysql_field_count() whether the statement was a SELECT.

std::string login;
std::cout << "login: ";
std::cin >> login;

std::string pol = "select * from table where login = '" + escapeStr(conn, login, '\'') + "';";
if (mysql_query(conn, pol.c_str()) != 0)
{
    std::cout << mysql_error(conn);
}
else if ((res = mysql_store_result(conn)) != NULL)
{
    while ((row = mysql_fetch_row(res)) != NULL)
    {
        std::cout << row[0] << " " << row[1] << " " << row[2];
    }
    mysql_free_result(res);
}
else if (mysql_errno(conn) == 0)
{
    std::cout << "there are 0 results";
}
else
{
    std::cout << mysql_error(conn);
}

Answer 2

From the documentation available in this site https://dev.mysql.com/doc/c-api/5.7/en/mysql-fetch-row.html

When used after mysql_store_result(), mysql_fetch_row() returns NULL if there are no more rows to retrieve.

so use that to verify whether the data has rows or not. Since doing this once would have fetched a row already, you need to print them immediately before trying to get another row from the DB.

row = mysql_fetch_row(res)
if( row == NULL )    // This verifies whether data is NULL or not
    cout << " There is no Results "<<endl 

else { 
    do
    {
        std::cout << row[0] << " " << row[1] << " " << row[2];
    }
    while (row = mysql_fetch_row(res)) != NULL)
}