How PHP prevents SQL injection from being explained

In our daily PHP development site to prevent SQL injection is the best to write a program, because it can give us the development of the site to protect the role, I believe many PHP novice programmers do not write the habit of preventing SQL injection, part may not, So today we'll talk about how to use PHP to prevent SQL injection.

Cause

On the one hand do not have this aspect of consciousness, some data has not been strictly verified, and then directly splicing SQL to query. Cause a vulnerability to occur, such as:

$id  = $_get[' id ']; $sql = "Select name from users WHERE id = $id";

Because there is no data type validation for $_get[' ID ', the injector can submit any type of data, such as unsafe data such as "and 1= 1 or". If you write in the following way, it's safer.

$id  = intval ($_get[' id "); $sql =" Select name from users WHERE id = $id ";

By converting the ID into an int type, you can get rid of unsafe things.

Validating data

The first step in preventing injection is validating the data, which can be rigorously validated against the appropriate type. For example, the int type can be converted directly to the intval:

$id =intval ($_get[' id ');

Character processing is more complex, first through the SPRINTF function format session output, to ensure that it is a string. Then some illegal characters are removed through some security functions, such as:

$str = Addslashes (sprintf ("%s", $str)); You can also replace addslashes with the mysqli_real_escape_string function

This will be more secure after processing. Of course, you can further determine the length of the string to prevent " buffer overflow attacks " such as:

$str = Addslashes (sprintf ("%s", $str)), $str = substr ($str, 0,40); Maximum length is 40

Parameterized bindings

A parameterized binding that prevents another barrier to SQL injection. PHP mysqli and PDO provide this functionality. For example, mysqli can query this way:

$mysqli = new mysqli (' localhost ', ' my_user ', ' My_password ', ' world '); $stmt = $mysqli->prepare ("INSERT into Countrylanguage VALUES (?,?,?,?) "); $code = ' DEU '; $language = ' Bavarian '; $official = "F"; $percent = 11.2; $stmt->bind_param (' SSSD ', $code, $language, $offi cial, $percent);

PDO is more convenient, such as:

/* Execute A prepared statement by passing an array of values */$sql = ' SELECT name, colour, calories from    fruit    W Here calories <: calories and colour =: colour '; $sth = $dbh->prepare ($sql, array (pdo::attr_cursor = pdo::cursor_fwdonly)); $sth->execute (Array (': Calories ' = ": Colour ' + ' red '); $red = $sth->fetchall (); $sth->execute (': Calories ' = 175, ': Colour ' =& Gt ' Yellow '); $yellow = $sth->fetchall ();

Most of us use the PHP framework for programming, so it's best not to spell SQL yourself and query by the framework given parameter bindings. When encountering more complex SQL statements, be sure to pay attention to the strict judgment when you spell it yourself. Not using PDO or mysqli can also write their own prepared, such as Wordprss DB query statements, you can see is also a rigorous type validation.

function prepare ($query, $args) {if (Is_null ($query)) return;    This isn't meant to being foolproof--but it'll catch obviously incorrect usage. if (Strpos ($query, '% ') = = = = False) {_doing_it_wrong (' wpdb::p repare ', sprintf (' the query Argume   NT of%s must has a placeholder. '), ' wpdb::p repare () '), ' 3.9 ');    } $args = Func_get_args ();    Array_shift ($args); If args were passed as an array (as in vsprintf), move them up if (isset ($args [0]) && is_array ($args [0]    )) $args = $args [0];         $query = Str_replace ("'%s '", '%s ', $query);         In case someone mistakenly already singlequoted it $query = Str_replace (' "%s" ', '%s ', $query); Doublequote unquoting $query = preg_replace (' | (? <!%)         %f| ', '%f ', $query); Force floats to be locale unaware $query = Preg_replace (' | (? <!%)         %s| ', ' '%s ', $query); Quote the strings, AvoidiNg escaped strings like%%s Array_walk ($args, Array ($this, ' escape_by_ref ')); return @ vsprintf ($query, $args);}

Summarize

Security is important, you can also see a person's basic skills, the project is flawed, extensibility and maintainability is no good. Usually pay more attention, establish a sense of safety, cultivate a habit, some basic security will certainly not occupy the time with coding. Develop this habit, even in the project urgency, short time situation, can still do high quality. Don't wait for the things that you are responsible for later, the database is taken away, causing the loss to be valued. Share!

Related articles:

A detailed explanation of how PHP prevents SQL injection

PHP to prevent SQL injection function introduction