Prevent SQL injection !

In the NorthWind Database the Customer table has a CustomerID field and the field is 5 long.


I am using the MyGeneration dOOdad architecture and I query the database for every customet that has a customerid of ‘ANTON’


In my code this looks like :



Dim cust As New Customers
cust.Where.CustomerID.Value = “ANTON –AND HERE IS SOME STUFF TOO–
cust.Query.Load()



So in the code I actually query on : ANTON –AND HERE IS SOME STUFF TOO–


But if I look in the SQL-Profiler I see the following :



exec sp_executesql N’SELECT * FROM [Customers] WHERE [CustomerID] = @CustomerID1 ‘, N’@CustomerID1 nchar(5)’, @CustomerID1 = N’ANTON


So the variable @CustomerID is cutoff at the correct length. This feature could prevent SQL Injection.


The more I look at the dOOdad architecture the more I like it.

Database Documenter MyGeneration Templates

On the MyGeneration WebSite there are  Templates that create database documentation in a very nice (and goodlooking) html format. Tables, Stored Procedures and such are placed in nice html documents.


They’re extremely easy to create and it’s really easy to browse all objects. Can come in pretty handy when you’re doing some coding and want a clean look at your database, or just need the extra documentation for a client or something.


To See what the database reports look like Click here….

dOOdad, Sorting in an ASP datagrid made easy

I have been playing around with the dOOdad architecture and  found out something very nice about this architecture.


Private Sub dgQuestions_SortCommand(Bla bla..) Handles dgQuestions.SortCommand


   ‘ Let’s get the Questions from the cache
   MyQuestions = Cache.Get(“MyQuestions”)

   ‘ Determine some sort orders
   Dim OldSortOrder As String = MyQuestions.Sort
   Dim NewSortOrderD As String = e.SortExpression + ” DESC”
   Dim NewSortOrderA As String = e.SortExpression + ” ASC”


   ‘ See if the sort is on a new column
   If OldSortOrder NewSortOrderA And OldSortOrder NewSortOrderD Then
    
‘ Sort is on a new column
    
MyQuestions.Sort = NewSortOrderD
   Else
    
‘ it’s on the same column, reverse the sort
    
If OldSortOrder = NewSortOrderA Then
       
MyQuestions.Sort = NewSortOrderD
    
Else
       
MyQuestions.Sort = NewSortOrderA
    
End If
   End If


   ‘Rewind the Questions
   MyQuestions.Rewind()


   ‘ Remove old instance from cache and add the Sorted one
   Cache.Remove(“MyQuestions”)
   Cache.Insert(“MyQuestions”, MyQuestions)


   ‘Bind the Grid
   BindGrid()


End Sub


The nice part about it is that this piece of code wil look the same for every datagrid. Only the BusinessClass will vary. And presto we have sorting on every column, Ascending and descending !.

MyGeneration Makes life Very easy….

March 16, 2005 / Comments Off on MyGeneration Makes life Very easy….

Well you prabably heard about MyGeneration by now if you are a regular BloggingAbout.Net reader. If you haven’t go and download it now here.


I am currently in the process of rewriting a web application and I am completely doing away with the old DAL. Instead I am using the dOOdad architecture that comes with MyGeneration.


Here are some of the advantages I found so far….



  1. About 40 % less code in the presentation layer ! ( So there is less to review as well )

  2. The code is much more readable.

  3. The Stored Procedures in the database all have a Uniform Name

  4. It’s Faster ! ( probably my old DAL wasn’t as fast as I thought)

  5. 0 lines of code in my DAL. Everything is handled in the generated Classes.

  6. I guess an overall code reduction of 70%

  7. Even the Dynamic SQL is handled in the correct SQL way (optimizing it for successive executes)

  8. Doing away with a lot of Stored procedures that would return a very specific subset

And since it’s a Web application you can do really nice stuff to the sort orders as well. Like the following….


       If Not IsPostBack Then
            Dim PubType As String = Request.QueryString.Item(“PubType”)
            If PubType = Nothing Then
                MyPublications.LoadAll()
‘ Load Them all
            Else
                MyPublications.Where.PublicationType.Value = PubType
                MyPublications.Query.Load() ‘ Load the specific subset
            End If
            ‘ Set Sort order

            MyPublications.Sort = MyPublications.ColumnNames.PublicationDate + ” DESC” 
            ‘ Add to cache
            Cache.Remove(“Publications”)
            Cache.Insert(“Publications”, MyPublications)
            BindGrid()
        End If


Then when you click on a header you can do stuff like….


    Private Sub dgPublications_SortCommand(ByVal source As Object, ByVal e As  System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgPublications.SortCommand
        ‘ Get from the cache
        MyPublications = Cache.Get(“Publications”)
        ‘ Set new sortexpression but keep results sorted on date
        MyPublications.Sort = e.SortExpression + ” , ” + MyPublications.ColumnNames.PublicationDate + ” DESC”
        MyPublications.Rewind()
        ‘ Add to cache, but first delete the old one
        Cache.Remove(“Publications”)
        Cache.Insert(“Publications”, MyPublications)
        BindGrid()
    End Sub


You don’t have to requey anything just set the sortcommand on the Entity, rewind the stuff and bind it again….
And you could do the same with filters although I haven’t used that yet….

Also a deletecommand on the datagrid is easy as …..


            Dim key As Integer = CInt(dgPublications.DataKeys(e.Item.ItemIndex).ToString())
            Dim DelPublication As New Publications
            DelPublication.LoadByPrimaryKey(Key)
            DelPublication.MarkAsDeleted()
            DelPublication.Save()
            Response.Redirect(“Start.aspx?PageID=ListPublications”)


Really easy ! No forgotten connetcions or stuff like that. and the code is still easy to read…….


Also note the Difference between the following….
           MyPublications.LoadByPrimaryKey(Key) ‘ Load the specific record
And
          MyPublications.Where.PublicationType.Value = PubType
     MyPublications.Query.Load() ‘ Load the specific subset
And
          MyPublications.LoadAll() ‘ Load them all !


This made me do away with a couple of stored procedures……….. Reducing the SQL Code as well…..


 

Awesome Dynamic SQL generation….

Hmmm,


Played around a littlebit with the dOOdad architecture of MyGeneration. Besides a lot of nice features there is also the the dynamic query feature…. Let me show what I mean…..


‘ See if it is in cache
Mymenu = Cache.Get(“DefaultMenu”)
If Mymenu Is Nothing Then
  
‘get the default Menu
  
Dim MyMenuID As New MenuID
  
MyMenuID.Where.MenuDescription.Value = “DefaultMenu”
  
MyMenuID.Query.Load()
  
‘Now get the MenuEntries for the menu !
  
Mymenu = New MenuData
  
Mymenu.Where.MenuID.Value = MyMenuID.ID
  
Mymenu.Where.MenuID.Operator = MyGeneration.dOOdads.WhereParameter.Operand.Equal
  
‘ Only those rows that we are Authenticated to see
  
Mymenu.Where.AuthenticationLevel.Value = Session(“AuthenticationLevel”)

   Mymenu.Where.AuthenticationLevel.Operator = MyGeneration.dOOdads.WhereParameter.Operand.LessThanOrEqual
   Mymenu.Query.AddOrderBy(Mymenu.ColumnNames.MenuOrder, ,myGeneration.dOOdads.WhereParameter.Dir.ASC)
  
Mymenu.Query.Load()
  
‘Cache this menu.
  
Cache.Insert(“DefaultMenu”, Mymenu)
End If
‘Bind to the grid
MenuGrid.DataSource = Mymenu.DefaultView
MenuGrid.DataBind()


In the blue piece of code we set some where parameters and add a sortorder. Everything resulted in the following query beeing executed :


SELECT * FROM [MenuData] WHERE [MenuID] = @MenuID1 AND [AuthenticationLevel] <= @AuthenticationLevel2 ORDER BY [MenuOrder] ASC


And this is a really nice query, nothing wrong with that !. I just was surprised how easy it was !.


 


 



 

Awesome Code generation !!!!

Have a look at MyGeneration. ( Click the link !!!!!!!!!!!, don’t miss out….. )


This is a freeware code generation tool that really works…..Normally I use the NorthWind database to test code generation and I test it in VB.Net and C#. Usually I have to modify stuff on some points to get the stuff working.


And now I came across this beauty !!!!.


Really try it…… !!! …………..There are several supported architectures but the best (until now) is the dOOdad architecure… (Yes click the link to see what it is………..)


I think it is really awesome….. I just pointed it to the NortWind Database and it worked at once…..
If you are interested in productivity tools have a look at it.


Oh yes and the beauty of it all…. It is all NATIVE .Net…. Not an ugly unknown DLL somewhere NO… everything is compiled. You only have to add a referrence to the dOOdad project.


Ok….. so far I told you to go and visit the website ….Here are some reasons why……..


Very easy dynamic queries………



Dim emps As New Employees
‘ LastNames that have “A” anywhere in them
emps.Where.LastName.Value = “%A%”
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
emps.Query.Load()


or



Dim emps As New Employees
‘ LastNames that have “A” anywher in them
emps.Where.LastName.Value = “%A%”
emps.Where.LastName.Operator = WhereParameter.Operand.Like_
‘ Only return the EmployeeID and LastName
emps.Query.AddResultColumn(Employees.ColumnNames.EmployeeID)
emps.Query.AddResultColumn(Employees.ColumnNames.LastName)
‘ Order by LastName
‘ (you can add as many order by columns as you like by repeatedly calling this)
emps.Query.AddOrderBy(Employees.ColumnNames.LastName, WhereParameter.Dir.ASC)
‘ Bring back only distinct rows
emps.Query.Distinct = True
‘ Bring back the top 10 rows
emps.Query.Top = 10
emps.Query.Load()


Easy Iteration



Dim emps As New Employees
If emps.LoadAll() Then
   Dim lastName As String
‘  Iteration walks the DataTable.DefaultView, see the FilterAndSort
‘ sample for further clarification.
   Do
      lastName = emps.LastName
   Loop Until Not emps.MoveNext
emps.Rewind()
   Do
      lastName = emps.LastName
   Loop Until Not emps.MoveNext
End If


Easy Bulk Update



Dim emps As New Employees
If emps.LoadAll() Then
‘ Modify the LastName column in every row
Do
emps.LastName = emps.LastName + “W”
Loop Until Not emps.MoveNext
‘ Rewind and mark the first row as Deleted
’emps.Rewind()
’emps.MarkAsDeleted()
‘ Add a new row and fill it in
emps.AddNew()
emps.FirstName = “Jimmy”
emps.LastName = “Lunch Box”
‘ Save all modifications, deletes, and new rows
emps.Save()
End If


And there are many many more templates based on the dOOdad architecture… Even screens are generated automatically !!!!!


 

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta