Ask Your Question
0

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

asked 2018-11-10 12:17:48 +0100

updated 2018-11-10 13:06:06 +0100

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:

image description

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-11-10 13:37:56 +0100

updated 2018-11-10 14:09:49 +0100

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
edit flag offensive delete link more

Comments

1

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
Ratslinger gravatar imageRatslinger ( 2018-11-10 17:56:30 +0100 )edit

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.

PhLo gravatar imagePhLo ( 2018-11-11 00:09:37 +0100 )edit
0

answered 2018-11-10 12:31:11 +0100

robleyd gravatar image

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.

edit flag offensive delete link more

Comments

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 gravatar imagePhLo ( 2018-11-10 12:37:05 +0100 )edit

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

PhLo gravatar imagePhLo ( 2018-11-10 13:00:19 +0100 )edit

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 gravatar imagePhLo ( 2018-11-10 13:15:40 +0100 )edit

So the prepared statement becomes something like

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

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

PhLo gravatar imagePhLo ( 2018-11-10 13:19:09 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-10 12:17:48 +0100

Seen: 34 times

Last updated: Nov 10