Case Sensitive or Insensitive SQL Query

-

Suppose you need to perform a SQL query and you need for it to be case sensitive or case insensitive, and either your database is set up the opposite way or you're smart and you're trying to write your query so that it will work regardless of how the database may or may not be configured. For instance, consider this query:

SELECT UserId, email
FROM UserInfo
WHERE email = 'ashvin@padhiyar.com'

If your database contains an email for Bill like this one: ASHVIN@padhiyar.com then whether or not your query will return any rows will depend on the COLLATION for the database. If you want to ensure that you DO get results, you can force it to use a CASE INSENSITIVE collation like so:

SELECT UserId, email
FROM UserInfo
WHERE email = 'ashvin@padhiyar.com' COLLATE SQL_Latin1_General_CP1_CI_AS

Similarly, if you don't want it to return any rows unless you specify the correct case for the email, you would change the collation to replace _CI_ with _CS_ where CI is short for Case Insensitive and CS is short for Case Sensitive. The following query would not match ASHVIN@padhiyar.com because it is using a case sensitive collation.

SELECT UserId, email
FROM UserInfo
WHERE email = 'ashvin@padhiyar.com' COLLATE SQL_Latin1_General_CP1_CS_AS

Also note that most of the time, passwords are expected to be case sensitive, so you can improve the security of your system by forcing any check comparing the password to use case sensitivity. However, if you're using hashed passwords (which you should be) then this is a non-issue since your hash algorithm will likely return all caps or all lowercase depending on how it is configured, and as long as you use the same hash settings when you set the password and when you check it, the collation of the database will not matter.

Comments

Post a Comment

Popular posts from this blog

Page Life Cycle in ASP.NET

-
Image
Life cycle when the page processed during a postback The events associated with the relevant page cycle phases are: Page Initialization: Page_Init View State Loading:LoadViewState Postback data processing: LoadPostData Page Loading: Page_Load PostBack Change Notification: RaisePostDataChangedEvent PostBack Event Handling: RaisePostBackEvent Page Pre Rendering Phase: Page_PreRender View State Saving: SaveViewState Page Rendering: Page_Render Page Unloading: Page_UnLoad The processing sequence in which a page is processed during a postback event is: Initializing: During this phase, the server creates an instance of the server control Loading view state: During this phase, the view state of the control posted by the client is reloaded into the new instance of the control. Loading : During this phase, the instance of the control is loaded onto the page object in which it is defined. Loading the postback data : During this phase, the server s
Read more

Managed Code and UnManaged Code

-
Managed Code is produced by (vb.net,c#,j#) .net framework language, which is under the control of CLR (Common Language Runtime).Garbage collector run automatically in managed code. Unmanaged Code is produced by third party language such as C++ can be used to write such applications, which does not run under the control of CLR.Garbage collector will not run in case of unmanaged code. Examples of Unmanaged code are Access low-level functions of the operating system. Background compatibility with code of VB, ASP and COM.
Read more

Print Function By JavaScript

-
function PrintContent( Obj) { PrintWin=window.open('','PrintWin','directories=no, location=no, menubar=no, resizable=no, status=no, toolbar=no, scrollbars=no, width=10, height=10, left=1050, top=800'); PrintWin.document.write(" "); PrintWin.document.write(document.getElementById(Obj.id).outerHTML); PrintWin.location.reload(); PrintWin.print(); PrintWin.close(); } /************* Function call ***************/ btnPrint.Attributes.Add( "OnClick", "PrintContent(" + TblPrint.ClientID + ");");
Read more

Select nth Row from Table in SQL Server

-
DECLARE @RowNumber INT = 9 WITH TableWithRow AS (       SELECT        ( ROW_NUMBER () OVER ( ORDER BY employees . EmployeeID )) as ROW ,        FirstName , LastName                 FROM employees ) SELECT * FROM TableWithRow WHERE ROW = @RowNumber Output: ROW                  FirstName  LastName -------------------- ---------- -------------------- 9                    Ashvin     Padhiyar (1 row(s) affected)  Note: ROW_NUMBER () is an analytic function. It assigns a unique number to each row to which it is applied, in the ordered sequence of rows specified in the order_by_clause , beginning with 1.  
Read more

SQL Server 2005 with XML Parameters

-
We frequently have requirement like Passing IDs in comma saperated value to the sql server for further processing. Generally what we do is just create loop and get the value from it and ... But SQL 2005 comes up with XQuery. Using this you can easly pass such type of data also some complex thing too. For example... DECLARE @productIds xml SET @productIds ='<Products><id>9</id><id>18</id><id>27</id></Products>' SELECT ParamValues.ID.value ('.','VARCHAR(20)')FROM @productIds.nodes('/Products/id')as ParamValues(ID) Which gives us the following three rows: 9 18 27
Read more

Variable Value in Top Statement

-
i want create a Procedure that behave like this: declare @cnt int set @cnt =10 select top @cnt * from sw_user Where sw_user contains 20 records, and i want the variable to be the criteria of Top statement. so i can use this for a solution for this type of problem CREATE PROC Top10 @cnt int AS SET ROWCOUNT @cnt SELECT * FROM sw_user but some cases ROWCOUNT might not be work... so what, Note : This statement however is possible in SQL 2005, just add "()" in your variable like so: declare @cnt int set @cnt =10 select top (@cnt) * from sw_user
Read more

Email Validation by Javascript

-
function EmailCheck() { var reg = /^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/; var str =document.getElementById('<%= TxtTo.ClientID %>').value; if(reg.test(str) == false) { alert('Invalid Email Address'); document.getElementById('<%= TxtTo.ClientID %>').focus(); } }
Read more

Overview of MVC

-
Overview of the ASP.NET MVC - Introduction to ASP.NET MVC -  ASP.NET MVC Version History -  Life Cycle of MVC request -  Role of Model, View, and Controller -  Works Behaviour of ASP.NET MVC -  Benefits MVC Architecture - Razor View Engine - Essential Language Features & Helpers - Basic Helpers - Strongly-Typed Helpers - Creating Custom Helpers - Declarative Helpers The MVC Pattern - The MVC Design Pattern - Working with Controllers - Model Templates - MVC State Management - Working with URLs and Routing (URL Routing) - Bundling and Minification Working with Controllers - Controller Actions - Controller and ActionResult Types - Passing Data from View to Controller - Action Filters & Custom Action Filter - Action Selectors HttpGet and HttpPost Razor Views - Razor View Engines and Syntax - Working with Layout Pages - Views & Partial Views - ViewData and ViewBag - Strongly-Typed Views - Working with Areas Model Binders - Behaviour
Read more