Prepared Statement Question Mark within WHERE LIKE '%?%'

[PhLo]

When writing a prepared statement in a Base macro, I can’t seem to replace the question mark within WHERE LIKE '%?%'. My guess is that it is because one might actually want to search for a question mark instead of replacing it in a prepared statement. Can I escape the question mark somehow so it can be replaced via .setString() in the prepared statement?

For example, take my generic function to find anything in any table and column:

Global Function findRecordsWhereSQL(sDatabaseTable As String, sWhereColumn As String, sWhereLike As String)
	Dim oForm As Object
	Dim oConnection As Object
	Dim oSqlStatement As Object 'the object that executes the SQL command
	Dim sSql As String 'the actual text of the SQL query/statement
	Dim oResult As Object

	oConnection = thisComponent.Drawpage.Forms(0).activeConnection()
	sSql = "SELECT * FROM ? WHERE ? LIKE '%?%' " 'base SQL
	oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
	oSqlStatement.setString(1, sDatabaseTable) 'insert search table
	oSqlStatement.setString(2, sWhereColumn) 'insert search column
	oSqlStatement.setString(3, sWhereLike) 'insert search term
	
	findRecordsWhereSQL = oSqlStatement.executeQuery()

	oConnection.close()
End Function

It causes an error, but I don’t know why. If I call the function like this:

Dim oResult As Object

oResult = findRecordsWhereSQL("account", "name", "John")

I get the following error:

Anyone know how to get around this problem so I can pass a function parameter to the WHERE LIKE '%?%' ?

If I execute the following statement directly in MySQL, it returns exactly the results expected, accounts with “John” as part of their name.

SELECT * FROM account WHERE name LIKE '%John%'

So how to get the function to pass my string variable into the prepared statement where the last question mark is?

[robleyd]

The error you get suggests that the error is immediately before account WHERE. Display the SQL string that is passed to MariaDB and see where the error is.

If this answer helped you, please accept it by clicking the check mark to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

[PhLo]

If I run the query SELECT * FROM account WHERE name LIKE '%John%' in MySQL/Maria, no errors. Is there a way to msgbox the final SQL prepared by a prepared statement to see if it adds some funky characters to the statement? I don’t know how to inspect its prepared SQL.

[PhLo]

I cleaned up my original question because it had some confusing typos.

[PhLo]

Aha, I see the problem, but I don’t know how to fix it. The setString() function automatically adds apostrophes before and after whatever you insert. This is a problem if you are inserting a column name, as MySQL interprets anything between apostrophes as a string literal, not a column name. I don’t know how to overcome this problem. I could just concatenate a statement and run it instead of using a prepared statement, but everyone knows that’s not as safe because of SQL injection.

[PhLo]

So the prepared statement becomes something like

SELECT * FROM 'account' WHERE 'name' LIKE '%'John'%'

because setString() adds apostrophes. Junk, how to resolve that?

[PhLo]

Figured it out, sort of.

All the examples of using prepared statements in LibreOffice Base macro documentation show the question marks on the right side of the equation (after the = equal sign), for example:

stSql = "UPDATE author " _
& "SET lastname = ?, firstname = ?" _
& "WHERE ID = ?"

Apparently it is not possible to replace column names and table names with ? and insert via function parameter. So you can’t replace author, lastname, firstname and ID in the example above with question marks and insert them via .setString(). Or if you can do that, I have found no such code examples online. Kinda lame. However, I came up with a compromise. It’s a hybrid between a prepared statement and not-so-prepared. The table and column name are concatenated, and the search term is prepared. I have to put the percentage sign %% wildcards within the passed parameter to make it work.

So my new function looks like this… still in development, but this much works, and the msgbox outputs the matching record names:

Global Function findRecordsWhereSQL(sDatabaseTable As String, sWhereColumn As String, sWhereLike As String)
	Dim oForm As Object
	Dim oConnection As Object
	Dim oSqlStatement As Object 'the object that executes the SQL command
	Dim sSql As String 'the actual text of the SQL query/statement
	Dim oResult As Object

	oConnection = thisComponent.Drawpage.Forms(0).activeConnection()
	sSql = "SELECT * FROM " & sDatabaseTable & " WHERE " & sWhereColumn & " LIKE ? " 'base SQL
	oSqlStatement = oConnection.prepareStatement(sSql) 'prepare statement
	oSqlStatement.setString(1, sWhereLike) 'insert search term

	findRecordsWhereSQL = oSqlStatement.executeQuery()
	
	While findRecordsWhereSQL.next
		sRecord = findRecordsWhereSQL.getString(1)
		msgbox sRecord, 0, "Record"
	Wend
End Function

Then, when I call the function, I have to include the %% wildcards IN the passed search term…

Global Sub findAccount()
	Dim oResult As Object
	
	oResult = findRecordsWhereSQL("account", "name", "%John%")
End Sub

[Ratslinger]

MySQL documentation:

Within the statement, ? characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it.

Nothing about using it for table or field names. As for the wildcards, maybe better to place in the setString line instead of every time you call the function:

oSqlStatement.setString(1, "%" & sWhereLike & "%") 'insert search term

[PhLo]

Thanks for the searchin’. I’m more accustomed to dealing with query parameter security in a different manner, and I’ve been out of the game for a few years. Concatenating the wildcards within the function is a good idea. I’ll do that unless I decide I want the function to be have more universal utility, as in WHERE column = someExactValue instead of WHERE column LIKE.