Prepared Statements - Yes I know it's a really discussed topic

Question

I know this is a really discussed thread, I've asked one question not so long ago AND it's a topic discussed countless times.

But, I'm still trying to secure a web app using prepared statements.

Truth is, never really got it to work with the answers given, looking at the duplicate question, looking at other questions and doing research.

It can't be that complex...

So, I have a lcl_events.php file, that starts with:

<?php include 'config/config.php'; ?>

<?php include'libraries/database.php'; ?>

The database.php file, looks like this:

<?php
 // Create connection
$mysqli= new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($mysqli->connect_errno) {
    die("Connection failed: " . mysqli_connect_error());
}

?>

The page loads fine, so far so good (no problems here).

Then, the file itself has also:

<?php

$sql = "select * 
from companies where Company_Name LIKE (?) OR Company_Subcategory LIKE (?) OR Keywords LIKE (?) OR Description LIKE (?) AND Company_Category = (?) AND Featured = `Y` order by Date_Created DESC";

/* Prepared statement, stage 1: prepare */
$stmt = $mysqli->prepare($sql);
if(!$stmt) {
    die("Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error);
}

/* Prepared statement, stage 2: bind and execute */
$target = $_GET['target'];
$company = $_GET['company'];
$category = $_GET['category'];
$target = '%'.$target.'%'; //this means the data coming from this GET method can have words before and/or after

$bind_result = $stmt->bind_param("sssss", $target, $target, $target, $target, $category);

$execute_result = $stmt->execute();
if(!$execute_result) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {}

/* explicit close recommended */
$stmt->close();
$mysqli->close();

The same process is repeated 6 times throughout the page.

(tried also to write the query without round brackets around the question mark.

The result is the following:

No errors appear in the logs or using:

error_reporting(E_ALL);

ini_set('display_errors', '1');

Note: I'm using PHPStorm and no errors are also showing up there.

Tired of this problem, it's taking quite a long time for what it is... really appreciate your help, I just want it to work.

The first thing that strikes me is that the query failed, most likely because `Featured = \`Y\`` is in backticks. Is `Y` really a column? If it's a string, you need to use singlequotes `'`. Also, its worth seeing what `$stmt->error` spits back at you. A sidenote: `(?)` is the exact same as `?`, you don't need the parenthesis. — Qirel, Apr 05 '17 at 22:10
Btw, why the brackets `(?)` for these? You're treating `LIKE` as a direct function when it's already a core MySQL method. Brackets are mostly used for subqueries. --- `var_dump()` your query and you'll see what's happening (or not). Make sure that all of your operators meet the query requirement. If one fails, the whole lot will. — Funk Forty Niner, Apr 05 '17 at 22:14
It's probably failing silently since the ticks are valid in a query as such, given "if" `Y` was a column and that you were trying to match something equaling to the `Featured` column. @tiagoperes — Funk Forty Niner, Apr 05 '17 at 22:18
@tiagoperes I posted a community wiki answer, using Qirel's comment and mine. I don't feel that rep should come of it, nor should I take full credit. You can mark it off as solved if you wish. — Funk Forty Niner, Apr 05 '17 at 22:25
See [AND and OR in WHERE](http://stackoverflow.com/questions/27663976/sql-statement-is-ignoring-where-parameter) and [when to use single quote, double quote, and backtick](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Barmar, Apr 05 '17 at 22:59

score 2 · Accepted Answer · edited May 23 '17 at 12:02

Posting the comments as a community wiki answer, since it was solved in comments.

By Qirel:

The first thing that strikes me is that the query failed, most likely because

Featured = `Y`

is in backticks. Is Y really a column? If it's a string, you need to use singlequotes '. Also, its worth seeing what $stmt->error spits back at you. A sidenote: (?) is the exact same as ?, you don't need the parenthesis.

By myself:

Btw, why the brackets (?) for these? You're treating LIKE as a direct function when it's already a core MySQL method. Brackets are mostly used for subqueries. --- var_dump() your query and you'll see what's happening (or not). Make sure that all of your operators meet the query requirement. If one fails, the whole lot will.

It's probably failing silently since the ticks are valid in a query as such, given "if" Y was a column and that you were trying to match something equaling to the Featured column, being a perfectly valid query, just not for this case though.

Also, when you mix `AND` and `OR` in a `WHERE` clause, you should always parenthesize it to make sure it matches your desired logic. The default precedence almost never does. — Barmar, Apr 05 '17 at 22:58

Prepared Statements - Yes I know it's a really discussed topic

1 Answers1