How to protect this code from sql injection?

Question

Hey all, I made a textbox which autocompletes input by getting stuff from a database and it works like this now:

<script type="text/javascript">
$().ready(function() {
    $("#food").autocomplete("get_course_list.php", {
        width: 260,
        cacheLength: 10,
        matchContains: false,

        //mustMatch: true,
        //minChars: 0,
        //multiple: true,
        //highlight: false,
        //multipleSeparator: ",",
        selectFirst: true

    });
});
</script>

and this in the .php file:

<?php
require_once "config2.php";
$q = strtolower($_GET["q"]);
if (!$q) return;

$sql = "select DISTINCT voedsel as voed from voedingswaarden where voedsel LIKE '%$q%'";
$rsd = mysql_query($sql);
while($rs = mysql_fetch_array($rsd)) {
    $cname = $rs['voed'];
    echo "$cname\n";
}
?>

But now i read about sql injections etc so I wanted to protect my php script against that with mysql_real_escape_string(); but I can't seem to get it to work. Any ideas how to implement this in my .php file and if this is enough protection?

I tried something like $q = strtolower($_GET["q"]); and then $q2 = mysql_real_escape_string('$q') and than use $q2 in the rest of the code. However then the autocomplete function wouldn't work anymore. — Javaaaa
– Javaaaa, Commented Jan 16, 2011 at 15:42
i think you just forgot to change variable name from $q to $q2. While it was unnecessary to use invent another variable. One is enough. Anyway it's just lack of attention, not some knowledge. — Your Common Sense
– Your Common Sense, Commented Jan 16, 2011 at 15:49

mario · Accepted Answer · 2011-01-16 16:10:45Z

1

$q = strtolower($_GET["q"]);

becomes

$q = mysql_real_escape_string(strtolower($_GET["q"]));

your connection to the db must be established and there must be only one link, but that is the case otherwise your mysql_query wouldnt work correctly.

the code is not very elegant but it'll work.

you may want to change that:

if (!$q) return;

to

if (strlen($q) == 0) return;

edited Jan 16, 2011 at 16:10

mario

146k20 gold badges243 silver badges293 bronze badges

answered Jan 16, 2011 at 15:38

The Surrican

30k24 gold badges127 silver badges168 bronze badges

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

6 Comments

Javaaaa Over a year ago

thanks, this works like a charm! about the last part, is that: first it checks if there is any string at all, and the second checks of there is a string with length 0? Why exactly is the last one better? (I'm trying to learn :)) and b.t.w is this enough protection against sql injections right now?

Your Common Sense Over a year ago

@Javaaaa it's enough for this very code, but it will fail somewhere else.

Javaaaa Over a year ago

what do you mean with somewhere else exactly?

The Surrican Over a year ago

for example if you use some wrappers for mysql or connection libraries, have multiple database connections etc.

Your Common Sense Over a year ago

@Javaaaa if your query goes to be SELECT voedsel FROM voedingswaarden WHERE id = $id it will fail to protect

|

Alfred · Accepted Answer · 2011-01-16 20:50:49Z

0

I have said this before but I think mysql_real_escape_string() should be depecrated and you should use PDO instead.

“PDO – PHP Data Objects – is a database access layer providing a uniform method of access to multiple databases.”

PDO is the new improved way to talk to your database. PDO has prepared statements which make your website faster/safer because:

A prepared statement is a precompiled SQL statement that can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks.

answered Jan 16, 2011 at 20:50

Alfred

62k33 gold badges151 silver badges186 bronze badges

2 Comments

Your Common Sense Over a year ago

it cannot make your site faster though

Alfred Over a year ago

I am (almost) certain it makes your site faster because the queries are precompiled!

Collectives™ on Stack Overflow

How to protect this code from sql injection?

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related