SQL connection crash on SQLExecute

Databases
1

Hi there,

I have a small app (Windows) to test a MSSQL Express connection.

MSSQL (SQL Server 2008 R2 10.50.4000) runs on a XP sp3 machine
Xojo 2013r3.1 IDE installed on both Win7 and XP machine, gives the same error on both OS

The following code can connect to the database but crashes on SQLExecute

No error in debugger, just XP crash with this error: “AppName: debugmon application.exe AppVer: 1.0.0.0 ModName: mssqlserverplugin.dll
ModVer: 0.0.0.0 Offset: 00011876”

Code to connect to SQL database: no problem here

[code] mDb = New MSSQLServerDatabase

mDb.Host = “192.168.1.102”
mDb.UserName = “xxxx”
mDb.Password = “xxxx”
mDb.DatabaseName = “DXDB”

If mDb.Connect Then
mIsConnected = True
ConnectStatusLabel.Text = “Connected to MS SQL Server”
Else
mIsConnected = False
ConnectStatusLabel.Text = "Error connecting to MS SQL Server: " + mDb.ErrorMessage
End If[/code]

Method to load database records in a ListBox - crash on stmt.SQLExecute:
Method is “loadAccounts(name As String)” crashes when name is “” or not “”

[code] DataList.DeleteAllRows

Dim sql As String
Dim stmt As MSSQLServerPreparedStatement

if name <> “” Then
stmt = mDB.Prepare("SELECT * FROM _Accounts WHERE acctName LIKE ? ")
stmt.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
stmt.Bind(0, “%” + name + “%”)
else
stmt = mDB.Prepare(“SELECT * FROM _Accounts”)
End If

stmt.SQLExecute
Dim data As RecordSet = stmt.SQLSelect

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.Field(“acctCode”).StringValue, data.Field(“acctName”).StringValue, _
data.Field(“City”).StringValue)

  data.MoveNext
Wend
data.Close

End If
[/code]

Any help welcome !

Thanks

2

You shouldn’t be calling stmt.SQLExecute at all! Remove that line from your code.

4

Thanks Tim.
Unfortunatelly, removing this line doesn’t solve the problem.

I guess I had added this to test something else.

Any other idea ?

5

Have you tried taking the prepared statement out of the equation and using a straight sql string? That would help narrow down the problem to whether it is the prepared statement or the database connection.

6

processing the record set like this work:

[code] DataList.DeleteAllRows

Dim sql As String
sql = “SELECT * FROM _Accounts”

Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.Field(“acctCode”).StringValue, data.Field(“acctName”).StringValue, _
data.Field(“City”).StringValue)

  data.MoveNext
Wend
data.Close

End If[/code]

But I need to be able to create more complex SQL statements, is there any other way to do it ?
Sorry, really newbie in both Xojo and MSSQL connection

7

You might want to look at <https://xojo.com/issue/16702>. I’ve just run into this and have had to change my database design. Ok if you are in full control, but if you are using someone else’s database you may have to abandon prepared statements.

8

You can create an sql string similar to how you specify the statement in the prepared statement with the following changes:

  1. You have to put single quotes around the string value – WHERE acctName LIKE ‘abcd’
  2. You should escape any single quotes in the value using ReplaceAll – name = ReplaceAll(name, “’”, “’’”)
    (that’s one single quote replaced by 2 single quotes)
  3. You will use string concatenation to insert the value of the variable, name, into the final string
dim sql as String
dim safeName as String = ReplaceAll(name, "'", "''")
sql = "SELECT * FROM _Accounts WHERE acctName LIKE '" + safeName + "'"
9

But doesn’t address the sql injection issue.

10

True, but you got to start learning somewhere.

11

Hi Tim, Wayne,

Thanks for your comments .
I saw feedback case 16702 yesterday but couldn’t imagine that a case dated April 2011 would still be unsolved :-((

With this kind of error I guess there not many people using the MSSQL Xojo plugin. That means that even if I use Tim’s proposal, I could face other problems later.
I have no control on the MSSQL database and there are about 300 tables, I should use a dozen.
I can’t risk being stuck during development later.

The software to develop could be a Web App, so I could deploy on a linux server and connect with ODBC but the feedback case shows similar problem using ODBC driver…

So what are the options ?
Using another tool than Xojo ? Too bad isn’t it ?

12

Basically I’ve found that using prepared statements with MSSQL just doesn’t work, but as Tim says you can use the SQLSelect & SQLExecute functions with the replaceall function to double the '.

The problem with sql injections can be overcome, but it does mean that at some stage you’re going to want to refactor your code to use Prepared Statements when it’s eventually fixed.

13

[quote=42314:@Olivier Colard]processing the record set like this work:

 DataList.DeleteAllRows
  
  Dim sql As String
  sql = "SELECT * FROM _Accounts"[/quote]

You’ve already proven that case #16702 isn’t a problem for you.

14

Hi Tim, Wayne,

Many thanks for your input.
As it seems that Tim’s trick is working, I’ll try this way.

SQL injection in current project should not be an issue as the app will be deployed on an intranet, on a rather waterproof network segment that has no access to the internet.

Regards,

Olivier

15

Let’s see if Xojo gets to look at their MSSQLServerDatabase - Plugin in 2014.
Apart from the above (where workarounds are available), there is another issue i see with transactions being called on different SPID’s ( Server Process ID’s).

So my list of MSSQLServerDatabase - Plugin issues is:
<https://xojo.com/issue/32121> (Transactions, SPID’s, RetainSameConnection)
<https://xojo.com/issue/16702> (SqlSelect crash, e.g. NTEXT fields)
<https://xojo.com/issue/16732> (SqlSelect crash, e.g. TEXT-field, which is (null))