CODEWITHSUNDEEP
phpsqlsql-server
James G.
James G.

Reputation: 2904

Where can parameters be used in SQL statements?

I'm building a search engine, and I've been experimenting with structuring parameterized SQL statements in PHP. I was wondering what the rules are for where parameters can be used.

e.g. This works:

$var = $unsafevar;
$stmt = mysqli_prepare($connection, "SELECT * FROM users WHERE username = ?");
mysqli_stmt_bind_param($stmt, 's', $var);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);

This doesn't:

$var = 'SELECT';
$var2 = 11;
$stmt = mysqli_prepare($connection, "? * FROM users WHERE username = ?");
mysqli_stmt_bind_param($stmt, 'ss', $var, $var2);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);

Where can parameters be used and where can they not be?

Or, more simply, what does paramaterization literally do to the variables? Does it put single quotes around them? If that is the case, is there a way to paramaterize wildcards, column names, or the SQL clauses themselves?

Upvotes: 0

Views: 85

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

My mnemonic for parameters is this:

Anywhere you could use a single scalar value, you can use a parameter.

That is, a quoted string literal, quoted date literal, or numeric literal.

Anything else (identifiers, SQL keywords, expressions, subqueries, a list of values for an IN() predicate, etc.) cannot be parameterized.

Upvotes: 2

Oded
Oded

Reputation: 499002

Database, schema, table and column names cannot be parameterized - you would need to resort to dynamic SQL if you wish to "parameterize" them.

The linked article is an extensive discussion on the use of dynamic SQL, mostly dealing with SQL Server though applicable to most SQL databases.

Upvotes: 3

Related Questions