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

Auto Sequence Number in Grid Control with Paging

-
we are going to see how to generate sequence number (row number) in DataGrid. To generate sequence number, we will use the DataGridItem’s ItemIndex property. But For example if we are going to second page with Pagesize 3, then sequence number should start from 4. Code snippet for implementing this method is... < asp:DataGrid id ="DataGrid1" runat ="server" PagerStyle-Mode ="NumericPages" PageSize ="10" AutoGenerateColumns ="False" AllowPaging ="True" > < Columns > < asp:templatecolumn headertext ="Row Number" > < itemtemplate > <% # (DataGrid1.PageSize*DataGrid1.CurrentPageIndex)+ Container.ItemIndex+1 %> </ itemtemplate > </ asp:templatecolumn > < asp:boundcolumn runat ="server" Da...
Read more

Polymorphism in C#

-
Polymorphism means having more than one form.   Overloading and overriding are used to implement polymorphism.  Polymorphism Examples Method Overloading Method Overriding There are two type of Polymorphism  1. Compile time polymorphism (Early binding or static binding)  Examples: overloaded methods, overloaded operators and overridden methods that are called directly by using derived objects. 2. Run time polymorphism (Late binding or dynamic binding)   Example: overridden methods that are called using base class object.  
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

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

How to Update Dependent Excel Cells Automatically

-
Image
I was facing some issue to update excel cell which was depended on some formula, I need to change 'Comparison_Rate_Calculator' by Programming and need to read Calculated value by formula '=+HLOOKUP(C3,Workings!$D$1:$AJ$2,2,FALSE)' over 'Comparison_Rate_Calculator_Values' column. I didn't get calculated value even i was able to change 'Comparison_Rate_Calculator' column's value. So I got solutions by refreshing Excel WorkSheet and Here is the Solutions. File: Web.Config < connectionstrings >   < add       name = " xls "       connectionstring = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AshvinWorkspace\POCs\ReadExcelSheet\ReadExcelSheet\Files\HLComparisonRateCalculator.xls;Extended Properties=Excel 8.0 " >   </ add > </ connectionstrings > File: Default.aspx.cs public partial class _Default : System.Web.UI. Page {     protected void ...
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...
Read more

Paged Data Source - Custom Paging

-
PagedDataSource , is a class that encapsulates the paging related properties for data-bound controls such as DataGrid, GridView, DataList, DetailsView and FormView that allow it to perform paging. And this article is going to combine both DataList control and PagedDataSource class to explain dynamic or custom paging methods for Asp.Net developers. protected void Page_Load( object sender, EventArgs e) { try { if (!IsPostBack) { LoadPhoto(); } } catch (Exception Exc) { HandleException = Exc; } } // LoadPhoto Function private void LoadPhoto() { PagedDataSource objPhotoPage = new PagedDataSource(); objPhotoPage.AllowPaging = true ; SOBLImageGallery _SOBLImageGallery = new SOBLImageGallery(); DataSet dsImageGallery = _SOBLImageGallery.Select6Records( new SOENTImageGallery()); if (dsImageGallery != null && dsImageGallery.Tables.Count > 0 && dsImageGallery.Tables[0]....
Read more