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

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

Abstract Class and An Interface

-
Abstract Class : The class which cannot be instantiated is known as an Abstract Class . So an object of it cannot be created. Hence it must be inherited. Can't be instantiated. Must be inherited. It may have Concrete Methods & Abstract Methods. An Abstract Class can have only Abstract Method. An Abstract Method must be overridden.     public abstract class AbstractA     {         public abstract string AbstractProperty { get ; set ; }         public abstract void AbstractFunction1();         public virtual void AbstractFunction2()         {             Console .WriteLine( "AbstractA : AbstractFunction2()" );         }         public void AbstractFunction3()         {             Console .WriteLine( "AbstractA : AbstractFunction3()" );         }         public void AbstractFunction4()         {             Console .WriteLine( "AbstractA : AbstractFunction4()" );         }     }     pu
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

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

Auto Refresh .aspx page - ASP.NET

-
Method 1: Response.AddHeader To Refresh Web page After every 5 Seconds You can add following code, Response.AddHeader("Refresh", "5"); Cricket Sites, Stock Exchange sites use similar logic :) Method 2: In body Tag, window.setTimeout The 1000 = 1 Second... < body onload ="window.setTimeout('window.location.reload()',1000);" > Method 3: In Meta Tag Theres also a meta tag that you can define on the head, but not sure wheter it refreshes even if the content has not finished loading or if it starts counting when the head section loaded. Code would be something like that: < meta content ="600" http-equiv ="refresh" > Method 4: Timer Control : Microsoft ASP.NET 2.0 AJAX Extensions server control In following example, the timer interval is set to 10 seconds <%@ Page Language="C#" AutoEventWireup="true" %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN&q
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