Stephen Mclaughlin Stephen Mclaughlin - 4 months ago 18
PHP Question

SQL injection proof SELECT with LIKE and wildcard into associative array

The plan is to create a public facing internet service that can autocomplete words from a database. I had it working successfully using a regular mysql query but I am concerned because I am going to be including this connection into a wordpress plugin that it will leave my server vulnerable.

I created a new user in MYSQL with only select permissions on this table so I am not worried about disclosing the password in the script as I believe it will help with debugging anyway. There are various echoes etc that I put there for debugging purposes. Eventually the output of this will go into a Json file and be retrieved by Jquery UI autocomplete.

Essentially the problem I am having is that I cannot get the query to work. I tried adding the code line by line but the connection doesn't work. Feel free to test the credentials on mysql workbench the IP address is 46.101.8.220. What is making this even more difficult to debug is that someone in the code is preventing anything appearing on the output of the page when viewed from a web browser. Any help would be greatly appreciated as I've spent many hours trying Mysqli, PDO, object oriented and procedural trying to get this to work.

$term = isset($_GET['term']) ? $_GET['term'] . '%': 'test%';
echo $term;

$conn = new mysqli('localhost', 'search-words','','admin_soup');
print ($conn ? 'connected' : 'cant con');
$query = 'SELECT id as value, word as label FROM word where word LIKE ?;';
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $term)
if ($stmt->execute()){
$result = $stmt->get_result();
while($row = $result->fetch_array(MYSQLI_ASSOC)){
$row_set[] = $row;
}
}
echo json_encode($row_set);//format the array into json data
$stmt->close();
$conn->close();
print_r($row_set);


After First advice code changed to and working. Thanks!

$term = isset($_GET['term']) ? $_GET['term'] . '%': 'test%';
echo $term;
$conn = new mysqli('localhost', 'search-words','','admin_soup');
print ($conn ? 'connected' : 'cant con');
$query = 'SELECT id as value, word as label FROM word where word LIKE ?;';
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $term);
if ($stmt->execute()){
$stmt->bind_result($value,$label);
while($row = $stmt->fetch()){
$row_set[] = array('value'=>$value, 'label'=>$label);
}
}

echo json_encode($row_set);//format the array into json data
$stmt->close();
$conn->close();


The code still doesn't return anything. Double checked the user information. The only thing I need to change when I am testing the sql statement is that I need to add quoations around the 'test%' but i think the bind_param doesnt need quotes. If i delete all the code from the $query downwars it will display the connected message on the screen if I add below that again. The screen shows nothing.

Is there perhaps something needing updated on my server or something?

Answer
$stmt->bind_param("s", $term)

Missing ";"

$result = $stmt->get_result();

The get_result() method is not always available. Check php.net: http://php.net/manual/en/mysqli-stmt.get-result.php

Available only with mysqlnd.

That is one of the reasons why pdo is so much more convenient...

But I guess you can use bind_result() and then fetch() the data as shown in the examples http://php.net/manual/en/mysqli-stmt.fetch.php

if ($stmt->execute()){
    $stmt->bind_result($value, $label);
    while($row = $stmt->fetch()){
        $row_set[] = array('value' => $value, 'label' => $label);
    }
}

By using PDO the code could look like this:

$stmt = $dbh->prepare('SELECT id as value, word as label FROM word where word LIKE ?');
$stmt->execute(array($term));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);