*.dannyg

BlogEngine.NET custom captcha

Dec
24

One of the biggest flaws with my blog was that there was no CAPTCHA  ("Completely Automated Public Turing test to tell Computers and Humans Apart.)  Wikipedia article here.

I was getting comment spammed.  This I hope will reduce if not eliminate BOTS from putting false comments into my blog.  BOTS do this to increase their ranking on search engines in order to get more hits to their sites, when a keyword is entered.

Upon looking at the code for BlogEngine.NET, I did notice that there looked like there was some DEV in the area of the CAPTCHA, but it wasn’t fully implemented.

There is still one minor security flaw in my CAPTCHA design.  Once it’s fixed I will reveal what it was and how I fixed it.  In the meantime, I’ve gone ahead and deleted all fake comments and uploaded the new code.

If you try to comment on my blog now, you’ll see a little picture with some number on it.  The picture is of one of my cats “Sneakers”.  If the number is typed incorrectly, you won’t be able to post the comment.  Some of the messaging needs fixing as well.  This is definitely a good feeling project for me.

image

Building a custom ORDER BY clause from a WHERE IN clause

Dec
08

I ran across a pretty interesting requirement.  Be able to sort something off a CSV list of items.  In this case we have a comma separated string we are using for an IN clause

declare @employeeids varchar(50)

set @employeeids = ‘4213,5321,4124’

declare @orderbyClause nvarchar(MAX)
select @orderbyClause = COALESCE(@orderbyClause, '') + ' when employee_id = ' + convert(varchar, number) + ' then ' + convert(varchar, roworder) from
dbo.iter$ordered_intlist_to_tbl(@employeeids)
select ‘ORDER BY CASE ' + @orderbyClause + ' END'
   

We are also using a custom table valued function which turns a CSV into a table and maintains the order using a roworder.

/****** Object:  UserDefinedFunction [dbo].[iter$simple_intlist_to_tbl]    Script Date: 12/08/2009 12:16:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[iter$ordered_intlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL, roworder int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int,
           @rowcounter int

   SELECT @pos = 0, @nextpos = 1, @rowcounter = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END – @pos – 1
      INSERT @tbl (number, roworder)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)), @rowcounter)
      SELECT @pos = @nextpos
      SELECT @rowcounter = @rowcounter + 1
   END
  RETURN
END
GO

Technorati Tags: ,,

Get SQL Table Sizes

Dec
07

Very powerful script for determine table sizesm the original blogger http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

DECLARE @TableName VARCHAR(100)    –For storing values in the cursor

–Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where  OBJECTPROPERTY(id, N’IsUserTable’) = 1
FOR READ ONLY

–A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

–Open the cursor
OPEN tableCursor

–Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

–Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    –Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC sp_spaceused @TableName

    –Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END

–Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

–Select all records so we can use the reults
SELECT *
FROM #TempTable

–Final cleanup!
DROP TABLE #TempTable

Technorati Tags: ,,

Use COALESCE to create a CSV

Dec
03

This has proved time and again to be a useful method of creating a datafeed.

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList
Technorati Tags: ,

Debug VBScript in EditPlus

Dec
03

See the below screenshot for configuring.

image

Technorati Tags: ,

Determine ASP.NET root page

Dec
03

Sometimes when an ASP.NET page loads up from the root of a directory, the page name is not revealed via HTTP headers.  If it’s a webforms application, simply scroll down the Postback form section. (id=”aspnetForm”)

Look at the action attribute.  See the sample below.

<form name="aspnetForm" method="post" action="Home.aspx" id="aspnetForm">
Technorati Tags: ,,,