How to avoid SQL Injection attack in this query?

1

In this case, I'm passing the WHERE through the variable $ where. Can the system undergo SQL Injection? How do you avoid it in this case?

For example: select nome from usuario where $where;

$where = "cod = 10";

public static function getUsuario($where){
    return Usuario::all(array(     
        'select' => 'nome',
        'conditions' => array($where)
    ));
}
    
asked by anonymous 05.05.2015 / 17:31

2 answers

3

Whether it can occur or not I do not know how to respond, it will depend on the framework used. But avoiding should be a lot easier, just validate the field, see if its format matches what you expect from it (and therefore will not contain malicious code, or any code that is, in> data ).

Its $where field is composed of the concatenation of $nome and $valor , right? A name usually consists of letters, numbers, and underscore ( _ ). A value is usually composed of digits, perhaps a minus in the front and maybe a dot. And I'm assuming you concatenate them using = , right? Then:

  • Validate $nome as a name; even though it comes from its own code (it costs nothing, it stays as "sanity test":

    if (preg_match("/^\w+$/", $nome)) {
        ...
    } else {
        /* Não continue! Reporte um erro! */
    }
    
  • Validate $valor as being a number; the following example is for a simple decimal, in Brazilian / European (non-American) format, adjust it according to your needs:

    if (preg_match("/^-?\d+(\.\d{3})*(\,\d+)?$/", $valor)) {
        ...
    } else {
        /* Não continue! Reporte um erro! */
    }
    
  • Concatenate $nome and $valor ; do not use quotation marks around the number :

    $condicao = $nome . " = " . $valor
    
  • Alternatively, if the $valor is a string that can contain - and / , in addition to letters, numbers, and underscore (and just this):

    if (preg_match("/^(\w|\/|-)+$/", $valor)) {
    

    And to concatenate:

    $condicao = $nome . " = '" . $valor . "'"
    

    In this case, we know that neither - nor / when within a string is sufficient to cause problems (a quotation mark or perhaps a \ ), used a LIKE (in which _ would be a problem), then it should be safe.

  • Etc. If $nome has a correct value, $valor has a correct value, the concatenation has the correct syntax, and all elements of its $where array have been assembled correctly, where an SQL Injection occurs. Whether the components have been "escaped" or not, it makes no difference, as the exhaust will leave them unchanged.

    Valuing values is a better alternative than accepting any input format and trying to sanitize it. Even sanitizing something you do not know is reckless (you've "escaped" correctly at the time of putting it on the bench, but by taking it out - and "unhooking it" - you included it in the response HTML, with no check, the result will be an XSS or similar ...). Use this strategy whenever the input data format is predictable and well defined, and you only have to worry about the more open fields (free text, for example).

        
    05.05.2015 / 19:40
    0

    This code only has SQL injection problems if this parameter comes from user input.

    If this does not come from the browser / user, that's fine.

    If it is user input, you should always use escape in php, or else parameters in queries, never direct input.

        
    05.05.2015 / 18:12