Expand|Select|Wrap|Line Numbers
- public bool LoginToSystem(string un, string pwd)
- {
- int count = 0;
- using (var conn = new SqlConnection("YourConnectionStringHere"))
- {
- string sql = "SELECT COUNT(userId) FROM users WHERE userName = '" + un + "' AND password = '" + pwd + "'";
- using (var cmd = new SqlCommand(sql, conn))
- {
- conn.Open();
- count = (int)cmd.ExecuteScalar();
- }
- }
- return count > 0 ? true : false;
- }
- SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution.
Given the above example we could provide the values [il]‘ OR ’1' = ’1[/il] for both username and password values and we would be able to log in no problems, as doing this would cause this to be executed in your database:
- SELECT COUNT(userId) FROM users WHERE userName = ” OR ’1' = ’1' AND password = ” OR ’1' = ’1'
Since 1 always equals 1 someone would then have access to your system, and trust me this isn’t something you want to have happen. That’s a mild result of what could happen if the above sql statement stayed the way it is, so this would then produce the following script to be executed in your database. With the way the statement is formatted someone could add ;DROP TABLE users-- and this, as you can image, would have devastating consequences. If that were to execute then this would be what your database sees:
- SELECT COUNT(userId) FROM users WHERE userName = ” AND password = ”; DROP TABLE users–
The semi-colon tells the database that one execution is ending and another is beginning, the – at the end of the statement tells SQL that the rest of the statement is a comment so ignore it. What would this do, well it would delete your users table.
So what can you do about this, well one of the biggest steps you can take to protect yourself is by using Parametrized Queries. Using parametrized statements embed your values into the statement, making it that much harder for someone to inject commands into your code.
So let’s take a look at how the above scenario can be remedied by using parametrized queries to protect yourself. Keep in mind this example will be for MSSQL, but I will also show how to do this for Microsoft Access as well. To do this we will use the AddWithValue Method of the SqlParameterCollection Class to create a parametrized query.
The new code (for MSSQL) would look like this:
Expand|Select|Wrap|Line Numbers
- public bool LoginToSystemParameterized(string un, string pwd)
- {
- int count = 0;
- using (var conn = new SqlConnection("YourConnectionStringHere"))
- {
- string sql = "SELECT COUNT(userId) FROM users WHERE userName = @username AND password = @password";
- using (var cmd = new SqlCommand(sql, conn))
- {
- conn.Open();
- cmd.CommandType = System.Data.CommandType.Text;
- //now add our parameters
- cmd.Parameters.AddWithValue("@username", un);
- cmd.Parameters.AddWithValue("@password", pwd);
- count = (int)cmd.ExecuteScalar();
- }
- }
- return count > 0 ? true : false;
- }
Expand|Select|Wrap|Line Numbers
- public bool LoginToSystemParameterized(string un, string pwd)
- {
- int count = 0;
- using (var conn = new SqlConnection("YourConnectionStringHere"))
- {
- string sql = "SELECT COUNT(userId) FROM users WHERE userName = ? AND password = ?";
- using (var cmd = new SqlCommand(sql, conn))
- {
- conn.Open();
- cmd.CommandType = System.Data.CommandType.Text;
- //now add our parameters
- cmd.Parameters.AddWithValue("@username", un);
- cmd.Parameters.AddWithValue("@password", pwd);
- count = (int)cmd.ExecuteScalar();
- }
- }
- }
- return count > 0 ? true : false;
- }
- sp_executesql. So to SQL Server this query looks like this
exec sp_executesql N’SELECT COUNT(userId) FROM users WHERE userName = @username AND password = @password,’N@username varchar(15),’N'@password varchar(25)’,@username=’yourname’,@password=’yourp assword’
So as you can see using parametrized queries can go a long way towards protecting yourself, your application & data, and your employer/users from malicious attacks from outside sources