0

I am trying to build a SQL query in a CGI script written in C. The user passes some sort of parameter when querying the page, and the script needs to query for:

SELECT `name`, `address`, `phone_number` FROM `restaurants` WHERE `name` LIKE '%{parameter}%'

I obviously can't just replace {parameter} with %s and sprintf into a string because of escaping and security issues.

Therefore I've constructed a prepared statement:

const char * statement = "SELECT `name`, `address`, `phone_number` FROM `restaurants` WHERE `name` LIKE '%?%'";

What ends up happening is that my question mark does not get parsed and the binding fails. When I change the end of the statement to "name LIKE ?", this works perfectly, except I lose the results containing the term rather than equaling it, which is bad.

Is it possible to get around this and still properly and securely construct SQL statements with LIKE's and parameters inside strings?

Thanks!

user1094786
  • 6,402
  • 7
  • 29
  • 42

3 Answers3

1

Try using LIKE ? in the query, and prepend/append the parameter string with % - I think that should work (it does via the PHP apis anyway, which might use the same library under the hood).

See the same problem in PHP: How do I create a PDO parameterized query with a LIKE statement?

Community
  • 1
  • 1
John Carter
  • 53,924
  • 26
  • 111
  • 144
0

The same problem exist using php and mysli. There the hack is to use sql function concat, e.g LIKE CONCAT('%',?,'%') Should work in C also..

user993553
  • 1,077
  • 5
  • 12
-1

why don't you just build a new query string with the strcat function? http://home.fhtw-berlin.de/~junghans/cref/MAN/strcat.htm It should solve your problem.

//EDIT: For example:

char *cmd = "SELECT `name`, `address`, `phone_number` FROM `restaurants` WHERE `name` LIKE '%":
char *cmdEnd = "%'";

// concat strings
strncat(cmd, parameter, strlen(parameter);
strncat(cmt,cmdEnd);
Sekis Onbes
  • 21
  • 5