Against SQL injection (PHP-MySQL)

[hipro1]

Well my doubt is, which are the best ways to protect my site from SQL injection ?
How and when to protect my forms ?

 

[descalzo]

I always use

$escaped_string = mysqli_real_escape_string ( $link , $unsafe_input_string )

where $link is the db connection returned by mysqli_connect and $unsafe_input_string is any info that comes from a form. Any info, even hidden fields.

 

[misson]

The more modern approach is to use prepared statements (see also mysqli:repare()):

$db = new PDO("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword);

# Named parameters
$stmt = $db->prepare("SELECT id, surname, given_name, birthday FROM users WHERE surname=:surname AND given_name=:given_name");
$stmt->execute(array(':surname' => 'Derf', ':given_name' => 'Fred'));
while ($row = $stmt->fetch()) {
    ....
}

// assumes $_POST has keys 'surname' and 'given_name'
for ($_POST as $key => $val) {
  $stmt->bindValue(":$key", $val);
}
$stmt->execute();
while ($row = $stmt->fetch()) {
    ....
}


# Positional parameters
$stmt = $db->prepare("SELECT id, surname, given_name, birthday FROM users WHERE surname=? AND given_name=?");
$stmt->execute(array('Derf', 'Fred));
while ($row = $stmt->fetch()) {
    ....
}

Of course, production code should look quite different. The DB user's credentials should be squirreled away in a function or class that creates DB connections, with the function/class itself squirreled away in a script that's only readable by the file's owner. The statement preparation, parameter binding and execution should be placed in a data access layer (DAL), so the other components of the site aren't aware of the database. Errors should be properly handled.

get_class_vars(), get_object_vars(), PDOStatement->fetchObject() and PDOStatement->setFetchMode(), along with the array functions, can all help translate between persistent storage and PHP objects.