Anatoly Lubarsky

T-SQL: LIKE Operator and Wildcard Characters

An interesting issue I came into recently. Suppose you have a search procedure - which consists of some SELECT query which gets a query phrase as a parameter and searches through a table column using LIKE.

The most common pattern is to add % at the beginning and the end of the search parameter so the query looks like so:

WHERE Col LIKE '%' + @param + '%'

Or you may perform concatenation before running the query as well :) However need to take into account the following. The search parameter which usually comes down to the procedure from the application user interface can contain T-SQL reserved wildcard characters such as => % [ ] _ etc. Such input will cause the query to return wrong results.

Therefore it is useful to remember to filter out or replace these characters so MSSQL will consider these characters literals. For example if we have rectangular braces => [], need to replace the opening brace => [ with the sequence => [[] before running the query.

MSSQL: Search and Replace in a TEXT(NTEXT) column

It's been a while since I've posted anything SQL related. So... Sometimes we need to search and replace a text value in the entire table. The column in question is of TEXT or NTEXT datatype. T-SQL REPLACE function does not work with TEXT/NTEXT datatype.

Instead we have to use several other functions:

UPDATETEXT - Updates an existing text, ntext, or image field.
TEXTPTR - Returns the text-pointer value that corresponds to a text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT, WRITETEXT, and UPDATETEXT statements.
PATINDEX - Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

A while ago I wrote a small utility procedure which uses above functions to implement Search and Replace functionality.

ALTER PROC dbo.SearchAndReplace 
(
     @FindString    NVARCHAR(100)
    ,@ReplaceString NVARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @TextPointer VARBINARY(16) 
    DECLARE @DeleteLength INT 
    DECLARE @OffSet INT 

    SELECT @TextPointer = TEXTPTR([MY_TEXT_COLUMN])
      FROM [MY_TABLE]

    SET @DeleteLength = LEN(@FindString) 
    SET @OffSet = 0
    SET @FindString = '%' + @FindString + '%'

    WHILE (SELECT COUNT(*)
             FROM [MY_TABLE]
            WHERE PATINDEX(@FindString, [MY_TEXT_COLUMN]) <> 0) > 0
    BEGIN 
        SELECT @OffSet = PATINDEX(@FindString, [MY_TEXT_COLUMN]) - 1
          FROM [MY_TABLE]
         WHERE PATINDEX(@FindString, [MY_TEXT_COLUMN]) <> 0

        UPDATETEXT [MY_TABLE].[MY_TEXT_COLUMN]
            @TextPointer
            @OffSet
            @DeleteLength
            @ReplaceString
    END

    SET NOCOUNT OFF
END

Hope this helps...

Facebook's Standard Security Settings Sufficient ? (Not)

BBC came out with an article today Identity 'at risk' on Facebook which discusses potential stealing of user data by 3rd parties applications of Facebook Development Platform:

"When you add an application, unless you say otherwise, it is given access to most of the information in your profile. That includes information you have on your friends even if they think they have tight security settings."

"Did you know that you were responsible for other people's security?"

Some Facebook applications developers have found this article misleading and "hilarious" - see the following thread: BBC: facebook apps dangerous.

However I find the article to be professional and points discussed there to be correct and valuable for the platform.

One important point developers missing is that the application in question is able to collect user friends data, while these friends are not aware of this application existing at all. No obvious OPT-IN for "friends".

Your friends don't need to install the application or ever login to it to have the info been collected.

Of course it is possible to not allow applications you haven't installed to get any info at all via user "Privacy Settings" page. Don't want to mention it is not obvious for the user he/she should do it. But are these settings secure "by default" ? I guess - no.

The BBC article points that out correctly:

"He said: "Facebook needs to change its default settings and tighten up security.""

"Default Settings" is very important security component of any system or application. In terms of "Default Settings" the good example is Internet Explorer. Think about how many default settings changes have been made in IE during recent years.

ASP.NET tip: Autonumber Rows in ASP.NET Repeater control

Q: How to print the current row number in an ASP.NET Repeater control ?

A: The easiest way to autonumber rows in Repeater is to use markup or ItemDataBound event. Since each row has ItemIndex property when created/bounded. Therefore for Repeater markup one can write the following:

< ItemTemplate>
    < %# Container.ItemIndex + 1 %>
< /ItemTemplate>

Or in the ItemDataBound event (for more complex rendering) this should look like so:

if ((e.Item.ItemType == ListItemType.AlternatingItem) || 
    (e.Item.ItemType == ListItemType.Item))
{
    int num = e.Item.ItemIndex + 1;

    // some code
}

Enjoy :)

Bathroom Stall Vandalism - 2000 Users on BEBO

Bathroom Stall Vandalism on BEBO has passed 2000 users. Which is good stats for the application with zero investment (just scaling up from facebook). Around 10% of them are active daily.

Reducing Memory Footprint of any Process with EmptyWorkingSet

Ever wondered why does .NET winform application takes so much memory on startup ? It is obvious that most of this memory is not in use. The reason is that JIT compiler and the whole winforms engine is loaded into the process. However it is possible to remove as many pages as possible from the working set of the specified process using EmptyWorkingSet from psapi.dll.

[DllImport("psapi.dll")]
static extern int EmptyWorkingSet(IntPtr hwProc);

From managed code we can use it like so (for the current process):

static void ClearMemory()
{
    EmptyWorkingSet(Process.GetCurrentProcess().Handle);
}

However it is possible to use this function for any process which handle must have the PROCESS_QUERY_INFORMATION and PROCESS_SET_INFORMATION access rights. Be careful using EmptyWorkingSet since the process will allocate the memory again in the future when needed.

C# Win32 API: Flash Window (FlashWindowEx)

Ever tried to flash a window using managed code ? The following method will flash the window title bar or the window taskbar button. Since there is no managed API available for this functionality - need to wrap a method imported from user32: FlashWindowEx:

[DllImport("user32.dll")]
static extern Int32 FlashWindowEx(ref FLASHWINFO pwfi);

Where FLASHWINFO struct is defined like so:

[StructLayout(LayoutKind.Sequential)]
public struct FLASHWINFO
{
    public UInt32 cbSize;
    public IntPtr hwnd;
    public Int32 dwFlags;
    public UInt32 uCount;
    public Int32 dwTimeout;
}

Where dwFlags can be one of the following:

// stop flashing
FLASHW_STOP = 0; 

// flash the window title 
FLASHW_CAPTION = 1; 

// flash the taskbar button
FLASHW_TRAY = 2; 

// 1 | 2
FLASHW_ALL = 3; 

// flash continuously 
FLASHW_TIMER = 4; 

// flash until the window comes to the foreground 
FLASHW_TIMERNOFG = 12;

So the typical C# call will look like so:

public static bool Flash()
{
    FLASHWINFO fw = new FLASHWINFO();

    fw.cbSize = Convert.ToUInt32(Marshal.SizeOf(typeof(FLASHWINFO)));
    fw.hwnd = this.Handle;
    fw.dwFlags = 2;
    fw.uCount = UInt32.MaxValue;

    FlashWindowEx(ref fw);
}

See some other C# Win32 API tips and tricks. Enjoy :)

Thoughts on Bebo Audience

Since one of my applications - Bathroom Stall Vandalism works both on bebo and on facebook as well - it is always useful to compare performance on both platforms. BTW, Bathroom Stall Vandalism is all about people virtually write and vandalize their rest rooms (LOL).

Need to take in to account that:

Bebo uses facebook's API model for its platform.
Facebook platform is more stable right now.
Facebook platform is more mature and much more competitive.
The app has the same canvas and database.
Bathroom Stall Vandalism was initially launched on facebook during october/november 2007 and was scaled up to support bebo in january 2008.

One can understand from the screenshot that the app is more popular among bebo users: they spend more time on site, have better bounce rate, etc.

Bebo audience is very special: most users are under 18 from UK, Australia and New Zealand.

Back from Thailand 2

I'm back again from Thailand.

The picture above is taken from Patong Beach, Phuket, Thailand.

Calculate Euler Number with Recursion

I recently went through several posts I had made on one of the development forums back then in 2005. I've found one of them interesting and wanted to repost it here. The question went like this:

"How can I calculate euler number with recursion ?"

Euler number (or e) can be calculated with the following formula in math:

sum = 1/0! + 1/1! + 1/2! + ... + 1/n!

Here one can notice the use of the Factorial function which can be implemented via recursion itself like so:

int Factorial(int n) 
{
    if (n == 0) 
    {
        return 1;
    }

    return n * Factorial(n - 1);
}

After we know how to implement Factorial we can implement Euler number using recursion as well:

double EulerNumber(int n) 
{
    if (n == 0) 
    {
        return 1;
    }

    return 1.0/Factorial(n) + EulerNumber(n - 1);
}

Enjoy :)