OleDB / MySQL Parameter Adding

Question

I have a question about adding parameters to a command (MySQL or OleDB, I currently use both) to avoid SQL injection.

This hard coded query works absolutely fine, aside from the open vulnerability to injection;

var queryString = string.Format("SELECT COUNT(*) FROM employs WHERE em_netname = '"+username+"' AND em_password = '"+password+"'");

However, after modifying the query so that I add parameters rather than leave the vulnerability open, it doesn't work. This is how I do it;

var queryString = string.Format("SELECT COUNT(*) FROM employs WHERE em_netname = @username AND em_password = @password");
OleDbCommand dbfQuery = new OleDbCommand(queryString, dbfCon);
dbfQuery.Parameters.Add("@username", OleDbType.Char).Value = username;
dbfQuery.Parameters.Add("@password", OleDbType.Char).Value = password;

Could somebody please give me an explanation as to why this is not working? The first query returns 1 from count, the second returns 0 (it should return 1).

EDIT: For clarity by "doesn't work" I mean that the first statement returns a column count of 1 i.e. there is a user, the query does work. The second statement, with exactly the same username and password entered returns a 0, i.e. although a username and password combination does exist (proven by the first statement) the query is not working correctly.

EDIT 2: Entire class code posted;

public static bool AuthenticateUser(string username, string password)
    {
        var constr = ConfigurationManager.ConnectionStrings["dbfString"].ConnectionString;           
        using (OleDbConnection dbfCon = new OleDbConnection(constr))
        {
            try
            {
                dbfCon.Open();
                var queryString = string.Format("SELECT COUNT(*) FROM employs WHERE em_netname = @username AND em_password = @password");
                OleDbCommand dbfQuery = new OleDbCommand(queryString, dbfCon);
                dbfQuery.Parameters.Add("@username", OleDbType.Char).Value = username;
                dbfQuery.Parameters.Add("@password", OleDbType.Char).Value = password;
                MessageBox.Show("Query: " + queryString);
                int numOfColumns = Convert.ToInt32(dbfQuery.ExecuteScalar());
                MessageBox.Show(numOfColumns.ToString());
                if (numOfColumns == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (OleDbException)
            {
                throw;
            }
        }
    }

What do you mean by _doesn't work_? You get any exception or error message? And how do you execute your queries exactly? Don't store your passwords as a plain text by the way. Read: http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — Soner Gönül, Oct 13 '15 at 08:14
Out of context - you are storing passwords in your database? — Marco, Oct 13 '15 at 08:15
@Serv unfortunately I have picked this project up from somebody else and yes, they were storing passwords in the database (it was written in Visual Fox Pro about 15 years ago). For now I am sticking with it but I am working on a whole new database schema. — CBreeze, Oct 13 '15 at 08:18
@SonerGönül I've edited again to add the class in its entirety — CBreeze, Oct 13 '15 at 08:20
You may be running into an issue where your OLEDB provider doesn't support named parameters. You may need to use positional placeholders with the ? character instead. To do so, just replace `@username` and `@password` with the ? character in the SQL query, and make sure the parameters are added to the parameter list in the correct order. — gigaplex, Oct 13 '15 at 08:24
did you also try it with varchar as oledbtype instead of char? ( — Thomas, Oct 13 '15 at 08:24

Biesi · Accepted Answer · 2015-10-13T08:29:11.667

1

According to MSDN the OleDbCommand does not support named parameters. Try to use ? instead.

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

edited Oct 13 '15 at 08:29

answered Oct 13 '15 at 08:25

Biesi

800
9
17

Microsoft has a habit of changing their links. You may want to copy/paste a relevant snippet from the documentation for historical purposes. – gigaplex Oct 13 '15 at 08:27
No my friend. MSDN is quite wrong about that. This provider **does** support named parameters. It just does not _care_ the parameter names. Only care about their orders. That means you **don't have to** use `?` for every parameter with this provider. – Soner Gönül Oct 13 '15 at 08:28
@SonerGönül Changing to `?` instead of `@username` did indeed work in this case – CBreeze Oct 13 '15 at 08:45

OleDB / MySQL Parameter Adding

1 Answers1