K4CZP3R YouTube K4CZP3R YouTube - 1 year ago 43
SQL Question

C# and MySql changing column var

I'm making mysql register/login system in c#. I'm able to register and login with it.
I'm verify account with:

MySqlConnection conn = new MySqlConnection(db_creds);
try { conn.Open(); }
catch { throw new Exception("Can't access database"); }
MySqlDataAdapter adapter;
DataTable table = new DataTable();
string query = "SELECT `Nickname`, `Password` FROM `" + db_table + "` WHERE `Nickname` = '" + nickname + "' AND `Password` = '" + password + "'";
adapter = new MySqlDataAdapter(query, conn);
if(table.Rows.Count <= 0)
return false;
else { return true; }

After Nickname and Password I have varchar named active. My question is:
How can I change "active" (only for this user) to 1 when user succesfully logged in? and when logoff change it to 0?

Answer Source

To alter a single row in the table, you need to get it's ID(unique identifier for this table). Let the name for this unique column be str_Id, then retrieve this id for the particular user name and password. Then you can update the active state based on this unique identifier.

Another important advise for you is, don't use this type of plain-text queries, which will opens a wide door for SQL Injection. So i strongly recommend you to use parameterized queries as follows;

string query = "SELECT Nickname,str_Id  FROM your_table_name" +
               " WHERE Nickname =@nickname  AND Password = @password";
MySqlConnection con = new MySqlConnection();

// Creating parameterized command

MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.Add("@nickname", MySqlDbType.VarChar).Value = nickname;
cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value = password;
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataTable table = new DataTable();

// Collect the details to a DataTable

if (table.Rows.Count>0) // Means there is some record found
    // Get theUnique ID for the matching record
    string uniqueId = table.Rows[0]["str_Id"].ToString();

    // Update active state for that particular user
    query = "Update your_table_name set active='0' Where str_Id=@str_Id";
    cmd = new MySqlCommand(query, con);
    cmd.Parameters.Add("@str_Id", MySqlDbType.VarChar).Value = uniqueId;
    // Execute command here
    // Print message thet no user found