Query comparing values for case sensitive

Question

I'm trying to compare the values for my password using this query but When I try using COLLATE SQL_Latin1_General_CP1_CI_AS it doesn't work.

Please assist me in this problem

Error Message

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

My Codes

OleDbConnection con = new OleDbConnection();
con.ConnectionString =     ConfigurationManager.ConnectionStrings["Connection"].ToString();
con.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "SELECT UserName, Password, ID FROM AdminLogin WHERE UserName='" + txtUserName.Text + "' AND Password='" + txtPassword.Password + "' COLLATE SQL_Latin1_General_CP1_CI_AS";
cmd.Connection = con;
OleDbDataReader rd = cmd.ExecuteReader();

If your trying to connect mdb or accdb access file then you don't use SQL Server collations — Siyon DP, Jul 13 '17 at 10:40
See [This artical](https://support.microsoft.com/en-us/help/244693/how-to-perform-a-case-sensitive-join-through-microsoft-jet?wa=wsignin1.0) from MS. — Siyon DP, Jul 13 '17 at 11:04
Notice you code is not secure and can easily be compromised ! — Siyon DP, Jul 13 '17 at 11:05
In other words, you can't trust everything on Stack Overflow. The accepted answer to this ms-access question [How to make SQL case sensitive](https://stackoverflow.com/questions/5747698/how-to-make-sql-case-sensitive) is **not correct**. COLLATE is not valid Access SQL. — C Perkins, Jul 13 '17 at 16:09

score -1 · Accepted Answer · answered Jul 13 '17 at 11:19

-1

You can use this option that will also increase security for SQL Injection

cmd.CommandText = "SELECT UserName, Password, ID FROM AdminLogin WHERE StrComp(UserName,'" + txtUserName.Text + "',0)=0 AND StrComp(Password,'" + txtPassword.Password + "',0)=0 ";

answered Jul 13 '17 at 11:19

Siyon DP

504
6
20

The fact that MS Access only supports executing one statement at a time already protects against SQL Injection attacks. But more generally, simply placing the user input "inside" a function call in the SQL is not a valid technique to thwart injection. If the database engine you're using allows multiple statements, the user input could still effectively include text that completes the first statement, injects their own SQL, then comments or nullifies the later parts. The best defense is to use parameters, which properly and effectively format the input as appropriate data types, etc. – C Perkins Jul 13 '17 at 16:01
@CPerkins How is the fact that Access executes one statment at a time helps against `Password = "1 Or 1=1"` Kind of sql injection attaks? – Siyon DP Jul 16 '17 at 10:43
It doesn't protect against all possible variations. That's why I mentioned using parameters. I should have qualified the statement with "many", "helps to", or something similar. But that fact _does_ prevent injection of _complete_ SQL statements, like DELETE or INSERT, along with the original statement. Now consider a password like `',0) OR StrComp('A','`, which would produce the valid SQL criteria `StrComp(Password,'',0) OR StrComp('A','A',0)=0 ` with your example. I explained my statement, so how about explaining why adding a call to StrComp increases security? – C Perkins Jul 16 '17 at 16:35
@C Perkins Well, I just said it increases security, not that it is perfectly safe. This is because after an attacker fails with the obvious `Password = "1 Or 1=1" ` he has no way to know what is the exact syntax of the query to bypass. Anyway the best thing to do is use input validation in addition to parameterizing the queries as stated [here](http://www.lavamunky.com/2011/11/why-parameterized-queries-stop-sql.html). – Siyon DP Jul 17 '17 at 11:10
Hmm... Yes, any step to make it more secure is an improvement. My concern is that when other simple ways exist to completely avoid all SQL injection, that your statement is a bit misleading to someone who obviously wasn't concerned with it to start. Any extra statements added to the SQL around the password would probably thwart the hacker's default password. But if someone is going to even consider SQL injection (which they always should), they should just learn how to do so completely. The best is to just state directly "This is not secure. Learn how to avoid SQL injection." – C Perkins Jul 17 '17 at 15:29

Query comparing values for case sensitive

1 Answers1