CODEWITHSUNDEEP
parameterspetapoco
Manight
Manight

Reputation: 510

PetaPoco paging with sql+parameter not working

I'm facing a very odd issue with PetaPoco while trying to return a paged query (SQL Server) with sql string + parameter. If I just use a plain SQL string it returns the resulsets as expected. If I use the string with @0 parameter no resulset is returned, even though if I inspect the SQL Command issued it is correct...

Here is the query

Dim query As PetaPoco.Sql = New PetaPoco.Sql("SELECT DISTINCT UserName FROM EVA_Users WHERE UserName LIKE '@0%' ORDER BY UserName", filterSql)
Dim result As PetaPoco.Page(Of AutoCompleteUserName) = db.Page(Of AutoCompleteUserName)(1, rowsNumber, query)

This should issue a command with "filterSql" as a string parameter for @0 quite simple and infact the SQL Command issued for the count is:

SQL Command: SELECT COUNT(DISTINCT UserName) FROM EVA_Users WHERE UserName LIKE '@0%' -> @0 [String] = "tes"

And for the pagin is: 

SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY UserName) peta_rn, peta_inner.* FROM (SELECT DISTINCT UserName FROM EVA_Users WHERE UserName LIKE '@0%' ) peta_inner) peta_paged WHERE peta_rn>@1 AND peta_rn<=@2 -> @0 [String] = "tes" -> @1 [Int64] = "0" -> @2 [Int64] = "10"

But with debugger I can check that in this scenario result.Items.Count = 0

If instead I don't use parameter but use a plain sql string it works as expected:

Dim query As PetaPoco.Sql = New PetaPoco.Sql(String.Format("SELECT DISTINCT UserName FROM EVA_Users WHERE UserName LIKE '{0}%' ORDER BY UserName", filterSql))

I also tried the version with SQL string+Params directly in the query but with no additional benefit, that way:

Dim result As PetaPoco.Page(Of AutoCompleteUserName) = db.Page(Of AutoCompleteUserName)(1, rowsNumber, query (with the @0 inside as a parameter), filterSql)

The DTO AutoCompleteUser is this one and come from a table with several more fields, from where I created this dto with just one field:

Imports PetaPoco

Namespace Eva.Entities.Core
<TableName("EVA_Users")> _
<PrimaryKey("", autoIncrement:=False)> _
<ExplicitColumns> _
<Serializable()> _
Partial Public Class AutoCompleteUserName
    Private mUserName As String
    <Column> _
    Public Property UserName As String
        Get
            Return mUserName
        End Get
        Set(value As String)
            mUserName = value
        End Set
    End Property
End Class
End Namespace

Any hint? I would like to use parametrized version to not handle myself sql injection scenarios. Thanks in advance

Upvotes: 0

Views: 1342

Answers (1)

Schotime
Schotime

Reputation: 15997

You need to put the % in the parameter value not the sql. The parameter should not be surrounded by single quotes ' either. eg.

Dim query As PetaPoco.Sql = New PetaPoco.Sql("SELECT DISTINCT UserName 
    FROM EVA_Users WHERE UserName LIKE @0 ORDER BY UserName", filterSql + "%")
Dim result As PetaPoco.Page(Of AutoCompleteUserName) = 
    db.Page(Of AutoCompleteUserName)(1, rowsNumber, query)

Upvotes: 1

Related Questions