SQL Injection's Secure Web Site Using ASP.net and Access Database

Question

Currently, I have a website with normal registration and login, encoded using ASP.net. I am using an Access database using the C # class that my friend wrote to handle most database operations (executeQuery, executeRead, isExits ...).

Now that I’ve almost finished creating my site, I want to start adding security - mainly to my database. I was looking for some time for a tutorial on this, but I could not find anything good except the old Microsoft MSDn article, which I could not really get for its code to work. The longest I have right now is simply the absence of any dangerous characters in the username and password (for example, ", -)), but it looks like this is the worst solution I can use (why my users aren’t use these characters?).

I think the best solution I found was to somehow insert the variables into the query string after the declaration (something like "WHERE username = @user" or something like that), but I could not get it to work with Access and with my oleDBManager.

here is my current registration code. handle () removes everything '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 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 can see, the main problem is that now the user cannot use characters like '. Suppose the best solution is to use @ variables in SQL queries, but I have no idea how to do this.

[thanks for your help] PS. I changed the name of my tables;)

edit: most of you tell 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 never did this

---

---

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 those who need it =]

Answer 1

Some notes

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

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

I can refer to this request by name in my code using the 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 if Access does the same thing as SQL Server, but in SQL Server you can avoid a single quote mark by doubling it:

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

Thus, you can include single quote marks in a string, you can save them in a database, and they cannot be used as malicious string terminators.