Secure website from SQL Injection ' using ASP.net and an Access database

Question

I currently have a website with a normal registration and login, coded with ASP.net. I am using an Access database, while using a C# class my friend wrote for handling most of the database actions (executeQuery, executeRead, isExits...).

Now that I've almost finished building my website, I want to start adding security - mostly to my database. I have searched for a while now for a tutorial on the subject, but I could not find anything good exept an old microsoft msdn article which I couldn't realy get its code to work. The furthest I've got now is just no allowing any dangerous characters in the username and password, (such as ',--,;), but it kind of feels as if it is the worse solution that i can use (why shouldn't my users use this characters?).

I think that the best solution I've found is somehow insertion the variables into the query string after declaring it (something to do with "WHERE username=@user" or something like that), but i couldn't get it to work with Access and with my oleDBManager.

here is my current registration code. handle() is removing all ' from the string, and Validate() checks for dangerous parts in the string.

        string username = user.Text;
        string password = pass.Text;
        bool isThingy = false;
        if (handle(ref password)) isThingy = true;
        if (handle(ref username)) isThingy = true;

        if (username != "" && username != null)
        {
            if (password != "" && password != null)
            {
                if (Validate(username, password))
                {
                    if ((db.IsExist("SELECT * FROM Table1 WHERE username='" + username + "'") == false))
                    {
                        int a = db.ExecuteQuery("INSERT INTO `Table1`(`username`, `password`, `logins`, `email`, `fname`, `lname`, `country`, `city`, `birthday`, `userid`) VALUES ('" + username + "', '" + password + "', '0', '', '', '', '', '', '', '" + Convert.ToString(Convert.ToInt32(db.ExecuteCellRead("SELECT MAX(userid) FROM Table1")) + 1) + "');");

                        if (!isThingy) errorLabel.Text = "Your user has been successfully registered";
                        else errorLabel.Text = "The ' token is invalid. your user was registered absence the '.";
                    }
                    else
                        errorLabel.Text = "This username is already taken";
                }
                else errorLabel.Text = "Invalid name format";

            }
            else errorLabel.Text = "Please enter a password";
        }
        else errorLabel.Text = "Please enter a user name";

as for the oleDBManager (named db in my code):

    private OleDbConnection link; // The link instance
    private OleDbCommand command; // The command object
    private OleDbDataReader dataReader; // The data reader object
    private OleDbDataAdapter dataAdapter; // the data adapter object
    private DataTable dataTable; // the data table object
    private string dbName; // the Database filename
    private int version; // the usersTableG office version
    private string connectionString; // the connection string for the database connection
    private string provider; // the matching driver string for the connection string
    private string path; // the path to the database file


...


    public int ExecuteQuery(string query)
    {
        this.link.Open();
        int rowsAffected;
        // ---
        this.command = new OleDbCommand(query, this.link);
        try
        {
            rowsAffected = this.command.ExecuteNonQuery();
        }
        catch (InvalidOperationException e)
        {
            if (e.Data == null)
                throw;
            else
                rowsAffected = -1;
        }
        finally
        {
            this.command.Dispose();
            this.link.Close();
        }
        // ---
        return rowsAffected;
    }

    public bool IsExist(string query)
    {
        this.link.Open();
        // ---
        this.command = new OleDbCommand(query, this.link);
        this.dataReader = this.command.ExecuteReader();
        bool a = this.dataReader.Read();
        // ---
        this.command.Dispose();
        this.link.Close();
        // ---
        return a;
    }

    public string ExecuteCellRead(string query)
    {
        string output = "";
        this.dataTable = this.ExcecuteRead(query);

        foreach (DataRow row in this.dataTable.Rows)
        {
            foreach (object obj in row.ItemArray)
            {
                output += obj.ToString();
            }
        }

        return output;
    }

So, as you might see, the main problem is that the user now can not use characters as '. It suppose the best solution would be using the @ variables in the SQL queries, but I have no idea how.

[thanks for your help] PS. i HAVE changed my tables' name ;)

edit: most of you are telling me to use these parameterized queries, but it would be great if you could give me an example of how to use them, since i've never done that

---

---

So, thanks to @Remou, my FINAL code is:

    db.DoWeirdStackOverFlowStuff(
    "INSERT INTO `Table1`(`username`, `password`, `logins`) VALUES (@username, @password, '0');"
    , new string[] { "@username", "@password" }
    , new string[] { username, password });

and

    public int DoWeirdStackOverFlowStuff(string query, string[] vars, string[] reps)
    {
        this.link.Open();
        int rowsAffected;
        // ---
        this.command = new OleDbCommand();
        this.command.CommandText = query;
        this.command.CommandType = System.Data.CommandType.Text;
        this.command.Connection = this.link;

        //Parameters in the order in which they appear in the query
        for (int i = 0; i < vars.Length; i++)
            this.command.Parameters.AddWithValue(vars[i], reps[i]);

        try
        {
            rowsAffected = this.command.ExecuteNonQuery();
        }
        catch (InvalidOperationException e)
        {
            if (e.Data == null)
                throw;
            else
                rowsAffected = -1;
        }
        finally
        {
            this.command.Dispose();
            this.link.Close();
        }
        // ---
        return rowsAffected;
    }

for whoever needs this =]

Answer 1

Some notes

In MS Access, I have a saved query called UpdateUser, it looks like this:

       UPDATE INTERNETSETTINGS 
       SET url = [@url], 
           databasename = [@databasename], 
           port = [@port], 
           username = [@username],
           [password] = [@password]

I can refer to this query by name in my code, using a command object:

        OleDbCommand Command = new OleDbCommand();

        Command.CommandText = "UpdateUser"; //saved query
        Command.CommandType = System.Data.CommandType.StoredProcedure;
        Command.Connection = cn; //a connection to the database

        //Parameters in the order in which they appear in the query
        Command.Parameters.AddWithValue("@url", "a"); //a,b,c etc for my test run
        Command.Parameters.AddWithValue("@databasename", "b");
        Command.Parameters.AddWithValue("@port","c");
        Command.Parameters.AddWithValue("@username", "d");
        Command.Parameters.AddWithValue("@password", "e");

        Command.ExecuteNonQuery();

Answer 2

I don't remember whether Access does the same thing as SQL Server here, but in SQL Server you can escape the single quote mark by doubling it:

username = username.Replace("'", "''");

So you can include single-quote marks in the string, you can store them in the database, and they can't be used as malicious string terminators.