MSSQLPreparedStatement not working

Hi all, I can’t seem to get prepared statements to work with MS SQL Server.

The following code returns an error from the Database: “Bind types were not specified, see BindType”. According to the language reference for MSSQLPreparedStatement, this should work.

Dim DB as MSSQLServerDatabase = ConnectToMSSQLServer() If DB <> Nil Then Dim ps as MSSQLServerPreparedStatement ps = DB.Prepare("SELECT id FROM users WHERE name = ? AND pass = ?") //<-----------Error! Dim users as Recordset = ps.SQLSelect("testuser", "testpassword") . . .

So I added bind types and now I get no error from the database, but my recordset is nil (i.e. nothing returned from the call to ps.SQLSelect():

Dim DB as MSSQLServerDatabase = ConnectToMSSQLServer() If DB <> Nil Then Dim ps as MSSQLServerPreparedStatement ps = DB.Prepare("SELECT id FROM users WHERE name = ? AND pass = ?") ps.BindType(0,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING) ps.BindType(1,MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING) ps.Bind(0,"testuser") ps.Bind(1,"testpassword") Dim users as Recordset = ps.SQLSelect() //<---------------Users is nil! . . .

The normal DB.SQLSelect() works just fine, but since this is part of an internet facing API I don’t want to expose my database to injection attacks. I find it hard to believe that prepared statements are totally broken so I’m more inclined to think i’m doing something wrong here.

FYI - I’ve filed <https://xojo.com/issue/39027> and it’s been in reviewed status for about a week. Also, I’m using: Windows 8.1 x64, Xojo 2015r2 & MSSQL Server 2014.

I wish I had access to a MSSQL database so I could test this for you, but alas, I do not. Your code looks right though.

But you should not be storing users’ passwords. Or is this just for testing?

Thanks Kem,

Yes, this is a contrived example and not the real code. In fact, I’m using your bcrypt implementation for hashing passwords. Thanks for that…:slight_smile: You saved me a ton of time converting those c routines.

After ps.SQLExecute(), do you check for errors?

Hi Johnny,

I did, no errors reported by the database.

I assume you mean ps.SQLSelect(). I don’t think ps.SQLExecute() would return a recordset.

No Jim, I’m sorry, I looked at your code quickly and thought you were doing an Insert and then a select to retrieve the inserted record. I guess I need to slow down a little.

I think I’ve got SQL Server installed on another laptop. I might start it up and see what results I get.

Thanks Johnny, I appreciate it. It would be nice to know I’m not going crazy here.

Jim I get the same results as you. Looks like it may be broken.

Could case be an issue? (Data is uppercase while you’re looking for lowercase, for example.) Have you tried a placeholder other than “?”? (Even though the MSSQL docs I found say “?” should work.)

Just spitballing here…

I’ve tried a very creative variety of stuff tonight… with no luck.

Have you tried switching to PostgreSQL?

:stuck_out_tongue:

I have used prepared statements with mssql without problem in the past, but I do use the sqlpreparedstatement type to make the statement db agnostic. Can’t see why there’d be a difference, but give it a try.

Also be aware that a bug in the MSSQL Native client causes issues with column types VarChar(Max) & Text along with others.

[quote=182509:@Kem Tekinay]Have you tried switching to PostgreSQL?

:P[/quote]
I wish I could, but not possible.

you could try MBS SQL Plugin as an alternative solution.

Something’s not right. If users is nil, then do.errormessage should have something in it. If there was no error, you would get an empty recordset. Could you try that again?

I think we’d rather see the real code as there may be something important that you left out in he translation.

Here’s code from a project with nothing in it but this code using SQL Server 2014 and Xojo 2015R2.
Using a prepared statement always causes the recordset to be nil. I changed SELECT to SELEC thinking
it might raise a database error, but it didn’t. No error until get to the nil recordset.

  dim db as new MSSQLServerDatabase
  
  db.Host = "127.0.0.1"
  db.Port = 1433
  db.UserName = "xojo"
  db.Password = "xojo"
  db.DatabaseName = "xojo"
  
  if db.Connect then
    dim sql as String = "SELECT uname FROM users WHERE pk = ?" 
    dim ps as MSSQLServerPreparedStatement
    
    ps = db.Prepare( sql )
    if db.Error then
      MsgBox db.ErrorMessage
    end if
    
    ps.BindType( 0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_BIGINT )
    ps.Bind( 0, 1 )
    
    dim rs as RecordSet
    rs = ps.SQLSelect()
    if db.Error then
      MsgBox db.ErrorMessage
    end if
    
    MsgBox rs.Field("uname").StringValue  << NilObjectException Here
    
    db.Close
  else
    MsgBox db.ErrorMessage
  end if

[quote=182534:@Greg O’Lone]Something’s not right. If users is nil, then do.errormessage should have something in it. If there was no error, you would get an empty recordset. Could you try that again?

I think we’d rather see the real code as there may be something important that you left out in he translation.[/quote]

Thanks Greg, and I agree, something is not right. Unfortunately, I can’t show the real code as that would compromise our authentication technique. The contrived example demonstrates the issue, as does Johnny’s complete example.

Consider trying to return all fields. If the RecordSet is still Nil, try returning all records by removing the Where clause. If it is still Nil, try varying the case of the table name and even the SQL syntax. Hopefully you will eventually get a RecordSet returned and can then gradually build the Select statement back up to narrow down the culprit.

Here is an untested variation of the sample code.

Dim db As New MSSQLServerDatabase db.Host = "127.0.0.1" db.Port = 1433 db.UserName = "xojo" db.Password = "xojo" db.DatabaseName = "xojo" If db.Connect Then Dim ps As MSSQLServerPreparedStatement = db.Prepare("SELECT * FROM users WHERE pk = ?") ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_BIGINT) Dim rs As RecordSet = ps.SQLSelect(1) If db.Error Then MsgBox db.ErrorMessage Else If rs = Nil Then // Nil RecordSet MsgBox "Nil RecordSet" Else If rs.EOF Then // End of File Condition MsgBox "End of File" Else MsgBox Str(rs.RecordCount) // Number of Records Returned While Not rs.EOF For Count_Integer As Integer = 1 To rs.FieldCount MsgBox rs.IdxField(Count_Integer).Name + " = " + rs.IdxField(Count_Integer).StringValue Next rs.MoveNext Wend End rs.Close // Don't forget to Close your RecordSet End End db.Close Else MsgBox db.ErrorMessage End

I would not bind to a literal
Try

    
    dim pk as integer = 1
    ps.BindType( 0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_BIGINT )
    ps.Bind( 0, pk )
    

Alternatively you can provide values in the select like

   rs = ps.SQLSelect(1)

Have you tried either / both of those ?
I’m in the same boat as kem not having a sql server to try