I have a routine where the content for the parameter “?” is flexible built. As a fictive example I would want to select the persons who have the hobbies “handball” and “swimming”.
The SQL would look like
SELECT * FROM tbl_user WHERE ?
The search parameter would be programmatically combined to
“hobby=‘handball’ AND hobby=‘swimming’”
Trying to bind this as MYSQL_TYPE_STRING won’t get a result. The reason might be, that the resulting prepared statement looks like
SELECT * FROM tbl_user WHERE 'hobby='handball' AND hobby='swimming''
In debug mode I’m not able to check the returned prepared statement, it doesn’t show any content.
Is there a possibility to debug a prepared statement?
[quote=237698:@Frank Kersten]I have a routine where the content for the parameter “?” is flexible built. As a fictive example I would want to select the persons who have the hobbies “handball” and “swimming”.
The SQL would look like
SELECT * FROM tbl_user WHERE ?
The search parameter would be programmatically combined to
“hobby=‘handball’ AND hobby=‘swimming’”
[/quote]
This wont work
Almost certainly correct
Bound variables are not for statements or fragments of statements but for the variables those statements need
Otherwise sql injection attacks are just as likely and the whole point of prepared statements is, in part, to make sql injection attacks harder if not downright impossible
To create a dynamic prepared statements you need to craft the “base” portion of your statement (SELECT * FROM tbl_user)
Then, in your code see how many criteria were provided & add one WHERE clause + bound variable marker per criteria
Hypothetically something like (this may or may not actually work as this is all written in the forum and not tested)
dim base_sql as string = "SELECT * FROM tbl_user"
dim clauses() as string
for i as integer = 0 to criteria.ubound
// lets assume criteria is an array of pairs with
// left = a key for the column
// right & a value for the value to search for
clauses.append criteria.left + " = ? "
next
dim sql as string = base_sql +If( clauses.ubound >= 0, " where " + join(clauses, "and "), "" )
dim ps as mysqldb.prepare( sql )
for i as integer = 0 to clauses.ubound
// define the prepared statement bund variables
// ....... note you need the type of the variable passed in to figure out how to bind this column
// ... and you may need the type of the db column so if you pass a string to
// select from an integer column you can do the right thing
next
dim rs as recordset = ps.sqlselect( ... variable for the select )