How to extract individual column data from C# MySQL query?

Question

How can I extract the column data from my user row? EX: This gets called on my WCF server when the client logs in. It works up to var xx = ds.Tables[0].Rows[1]; where it throws an error on the clients side. Basically I am trying to have the user/pass verified in the database. Then return to the Client a DateTime of when his subscription expires.

public bool Authenticate(string userId, string password, out string token)
    {
        token = "";

        string MyConnectionString = "Server=localhost;Database=testdb;Uid=root;Pwd=admin;";
        MySqlConnection sqlCon = new MySqlConnection(MyConnectionString);
        sqlCon.Open();

        MySqlCommand cmd = sqlCon.CreateCommand();
        cmd.CommandText = "SELECT * FROM table1 WHERE username = '"+userId+"' AND password = '"+password+"'";
        MySqlDataAdapter adap = new MySqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adap.Fill(ds);


        if (ds.Tables[0].Rows.Count > 0)
        {
            token = Guid.NewGuid().ToString();
            var xx = ds.Tables[0].Rows[0];


            CustomDataSource.AddUserData(token, userId);

            return true;
        }

        return false;
    }

score 2 · Answer 1 · edited May 23 '17 at 12:27

Well I suppose that your query returns only one row (if it finds the user with the correct password)

In that case you get the date from the first row returned (index zero).
Also I assume that your date is stored in the fifth column (index four), if not you should adjust the second index to point to the correct column. (The base array index is always zero)

if (ds.Tables[0].Rows.Count > 0)
{
    token = Guid.NewGuid().ToString();
    var xx = ds.Tables[0].Rows[0][4];
    CustomDataSource.AddUserData(token, userId);
    return true;
}

Said that, let me point to a big problem of this code.
This code could be easily used for a Sql Injection Attack because it concatenates strings, probably typed by your user, to form a Sql Text passed to the database engine. Instead you should use parameters to avoid the Sql Injection problem and the quoting of user text (password with an apostrophe?)

    using(MySqlConnection sqlCon = new MySqlConnection(MyConnectionString))
    {
        sqlCon.Open();
        MySqlCommand cmd = sqlCon.CreateCommand();
        cmd.CommandText = "SELECT * FROM table1 WHERE username = ?user AND password = ?pwd";
        cmd.Parameters.AddWithValue("?user", userId);
        cmd.Parameters.AddWithValue("?pwd", password);
        using(MySqlDataAdapter adap = new MySqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();
            adap.Fill(ds);
        }
    }

score 0 · Answer 2 · answered Nov 08 '12 at 23:46

0

var xx = ds.Tables[0].Rows[0].ItemArray[5];

Is how.

answered Nov 08 '12 at 23:46

Vans S

1,743
3
21
36

score 0 · Answer 3 · answered Nov 08 '12 at 23:51

try using foreach loop

foreach (DataRow row in ds.Tables[0].Rows) 
{
    var xx = row[1];
    var x = row[5];

    // other codes

    return true;
}

one more thing, parameterized your query to avoid SQL injection

using(MySqlConnection sqlCon = new MySqlConnection(MyConnectionString))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        cmd.Connection = sqlCon;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT * FROM table1 WHERE username = @user AND password = @pass";
        cmd.Parameters.AddWithValue("@user", userId);
        cmd.Parameters.AddWithValue("@pass", password);
        using (MySqlDataAdapter adap = new MySqlDataAdapter(cmd))
        {
            try
            {
                DataSet ds = new DataSet();
                adap.Fill(ds);
            }
            catch (MySqlException e)
            {
                // do something with the exception
                // don't hide it!
            }
        }
    }
}

How to extract individual column data from C# MySQL query?

3 Answers3