How to avoid SQL Injection?

[eluser]Sinclair[/eluser]
Hi,

I have some model functions that are not protected against SQL injections.

Here is an example:

Code:

function getAnunciosZonaNormais($pzona) {

        $query = $this->db->query("select a.id_anuncio, a.n_anuncio, a.id_foto_anuncio,
                                   fa.n_ficheiro
                                   from aa_anuncios a,
                                   aa_anuncios_detalhe ad, ap_pag_anuncios pa,
                                   aa_anuncios_anunciantes aa, ae_anunciantes ae,
                                   aa_fotos_anuncios fa, aa_cidades c
                                   WHERE
                                   a.id_anuncio = ad.id_anuncio and
                                   a.id_anuncio = pa.id_anuncio and
                                   a.id_anuncio = aa.id_anuncio and
                                   aa.id_anunciante = ae.id_anunciante and
                                   fa.id_anuncio = aa.id_anuncio and
                                   fa.id_foto = a.id_foto_anuncio and
                                   ad.id_cidade = c.id_cidade and
                                   pa.dat_inicio <= now() and
                                   (pa.dat_fim >= now() or pa.dat_fim is NULL) and
                                   c.id_distrito = '".$pzona."' order by random()");
        return $query->result();
    }

How can I protect this function against SQL injections?


Best Regards,

[eluser]JHackamack[/eluser]
if you're only have one variable you can do the following.

mysql_real_escape_string() around your paramater


http://www.php.net/manual/en/function.my...string.php

[eluser]Sinclair[/eluser]
The database that I'am using is PostgreSQL.

Best Regards

[eluser]kgill[/eluser]
Simple it's all about where $pzona is coming from, has it been validated on the server after it was posted to ensure it's what it is supposed to be? If not, do that. SQL injection only works when you the coder pass input directly from the user to the SQL statement without checking it and ensuring it's valid and quoted properly.

[eluser]Sinclair[/eluser]
[quote author="kgill" date="1263184780"]Simple it's all about where $pzona is coming from, has it been validated on the server after it was posted to ensure it's what it is supposed to be? If not, do that. SQL injection only works when you the coder pass input directly from the user to the SQL statement without checking it and ensuring it's valid and quoted properly.[/quote]

Hi,

$pzona is comming from URL, its is a parameter that is passedin the URL. How can I protect against?

Best Regards,

[eluser]kgill[/eluser]
You glossed over the rest of my post, what you need to do was spelled out there: Ensure it's valid and quoted properly...

[eluser]JHackamack[/eluser]
If you're using PostgreSQL you could do a simple php.net search to find:

http://php.net/manual/en/function.pg-escape-string.php

pg_escape_string

[eluser]Random dude[/eluser]
I think you should consider using Active Record in CI - it simplifies your db coding greatly, escapes strings automatically, and is db independent.

http://ellislab.com/codeigniter/user-gui...ecord.html

[eluser]Colin Williams[/eluser]
The query() method takes a second parameter, an array, whose contents will replace '?' in the query. The contents of the array you pass will be sanitized. Example:

Code:

$this->db->query('SELECT * FROM ? WHERE name = ?', array($table, $name));

This produces a safer version of

Code:

$this->db->query("SELECT * FROM $table WHERE name = $name")