Securing PHP MySQL code

Question

I have a PHP search suggestion script which uses MySQL as its back-end. I am aware there are many vunerabilities in my code, I was just wondering what I can do to make it more secure.

Here is my code:

<?php
$database=new mysqli('localhost','username','password','database');
if(isset($_POST['query'])){
    $query=$database->real_escape_string($_POST['query']);
    if(strlen($query)>0){
        $suggestions=$database->query(
            "SELECT * FROM search WHERE name LIKE '%" . $query .
             "%' ORDER BY value DESC LIMIT 5");
        if($suggestions){
            while($result=$suggestions->fetch_object()){
                 echo '<a>'.$result->name.'</a>';                       
            }
        }
    }
}
?>

In what sense do you want to make it more secure? Since you are already escaping the search string, what is it you are looking to secure? — Wilbo Baggins
– Wilbo Baggins, Commented Jun 1, 2011 at 23:41

dynamic · Accepted Answer · 2011-06-01 23:48:59Z

4

Actually there aren't, considering you are escaping the only external value in the SQL

Anyway I suggest you to use PDO::prepare for queries. Go here for further infos

http://it.php.net/manual/en/pdo.prepare.php

Example:

$sth = $dbh->prepare('SELECT * FROM article WHERE id = ?');
$sth->execute(array(1));
$red = $sth->fetchAll();

edited Jun 1, 2011 at 23:48

answered Jun 1, 2011 at 23:40

dynamic

48.2k56 gold badges163 silver badges236 bronze badges

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

Comments

Tomasz Kowalczyk · Accepted Answer · 2011-06-01 23:42:31Z

3

Some tips from me:

use PDO,
don't concatenate query parameters, use prepared statements in PDO,
don't put "*" in SELECT statement, get only the columns you'll need,
use fetchAll() in PDO, don't fetch records in while() loop.

answered Jun 1, 2011 at 23:42

Tomasz Kowalczyk

10.5k6 gold badges56 silver badges70 bronze badges

1 Comment

dynamic Over a year ago

I am not the downvoter But I could try to explain why they downvoted: the OP asked not tips but if there were vulernatibiles. he didn't asked for PDO fetchAll neither for SQL optimization with SELECT *. Maybe it's for that

Collectives™ on Stack Overflow

Securing PHP MySQL code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related