Mysqli PHP prepared statement when the query uses SQL variable

Question

I wanted to make my SQL query into a prepared statement but realized, that it wasn't as easy as I first thought. Here is the query in its current form, so not as prepared statement.

$mysqli = new mysqli(...);

$result = mysqli_query( $mysqli,
"SELECT count(*) as total from test_users, image_uploads 
    WHERE test_users.APPROVAL = 'granted'
    AND test_users.NAME = image_uploads.OWNER
    AND (test_users.IMGAUTO = 'enabled' OR image_uploads.IAPPROVAL = 'granted')
");
$data = mysqli_fetch_assoc( $result );
$row_cnt = $data['total'];
$totalPages = ceil(($row_cnt / $cardmax));

So my problem now is this. When I make the prepared statement, I'm not going to be able to access image_uploads.OWNER anymore since I use it inside the query at the moment.

$grant = 'granted';
$owner = ""; //<<--- how to get image_uploads.OWNER
$enabl = 'enabled';

$sql = 
"SELECT COUNT(*) FROM test_users, image_uploads
    WHERE test_users.APPROVAL=?
    AND test_users.NAME=?
    AND (test_users.IMGAUTO=? OR image_uploads.IAPPROVAL=?)
";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ssss', $grant, $owner, $enabl, $grant);
$stmt->execute();
$row = $stmt->get_result()->fetch_row();
$row_cnt = $row[0];
$totalPages = ceil(($row_cnt / $cardmax));

Is there a method to get this image_uploads.OWNER in my prepared statement. How do I do this correctly?

@CrandellWS no, you've entirely missed the point of the question — ADyson
– ADyson, Commented Jan 16, 2020 at 23:08

ADyson · Accepted Answer · 2020-01-16 23:06:28Z

3

It looks like image_uploads.OWNER is a column in a SQL table in your database. If so, then you can just leave it as it is in your first version. You don't need to parameterise it.

Only data values coming from outside the database engine (e.g. user input, or data from a file) need to be parameterised, because that data is potentially unknown and could contain malicious values / injection attacks which need to be sanitised against. Quoting a column name from another table cannot pose such a threat - you are not putting an unknown string value into the query syntax.

N.B. In actual fact none of your original query relies on external input or unknown variables - all of the data is hard-coded into the query text. So you don't actually need to parameterise anything at all in this particular query. Everything is set in advance, so there is no threat from surprise / unknown text becoming part of the executable SQL.

edited Jan 16, 2020 at 23:06

answered Jan 16, 2020 at 23:03

ADyson

62.9k16 gold badges88 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Miger Over a year ago

very helpful, thanks! ok, then I can just leave it as it is.. that's great! Thanks again for the insight, I will go through my code again and keep that in mind.

Collectives™ on Stack Overflow

Mysqli PHP prepared statement when the query uses SQL variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related