Precisely and in detail about injection protection in php, mysql?

Question

PHP

Hello! I rummaged through a bunch of different information about protecting user-entered data, but I did not find an exact explanation of how to properly protect myself. On forums, guides and other sources of information, everything always comes down to one thing - a person writes a guide on how to "correctly" defend himself, then in the comments people write that he is a fool and only teaches people bad things. As a result, no one left normal and accurate information.
Let's take as an example such a task, the most elementary and which is constantly talked about - password protection, login on the authorization form. Someone writes that you need to do this:

$login = trim($_GET['login']);
  $login = strip_tags($login);
  $login = mysqli_real_escape_string($mysqli, $login);

And someone you need like this:

$login = $_GET['login'];
  $login = mysqli_real_escape_string($mysqli, $login);

As a result, all these options are "rejected". I never received an answer. In addition, even if you "google" information on my request, articles with a negative rating are always in the first places (From habr, for example). Perhaps the question is very simple, but after reading all the articles, I am completely confused. Can anyone provide an answer on this? Please do not write about the need to use ready-made CMS and other solutions.
Thank you!

Answer 1

$login = trim($_GET['login']);
$login = strip_tags($login);
$login = mysqli_real_escape_string($mysqli, $login);
Why do you think that you need to change the user's login if she wanted just that? wants to be <script>- let it be. Don't limit.
you need to distinguish between sql injection and xss attack
i.e. if we write to the database, then
$login = mysqli_real_escape_string($mysqli, $login);
query( insert into ... );
Next, we display on the page
$login = query( select from ... );
echo htmlspecialchars($login);
And everyone is happy.
Too lazy to write the code, although I basically already wrote it above =) but instead of mysqli_real_escape_string in real code, I would use prepared expressions (and PDO instead of mysqli, although this is probably a matter of taste).

Answer 2

Read the article   habrahabr.ru/post/148701
It describes in detail why what you are doing (these two pieces of disgusting code that you have given) is unacceptable and leads to SQL injections.

Answer 3

Validation of input variables is done in 2 stages:
1. validfilter() - Check input variables from users for validity using Regex.
2. mysql_escape_mimic() - Escaping before placing a query to the database in the expression.
(working code!)

//----------------------
function mysql_escape_mimic($inp) {
//from: http://php.net/manual/en/function.mysql-real-escape-string.php#101248
    if(is_array($inp))
        return array_map(__METHOD__, $inp);
    if(!empty($inp) && is_string($inp)) {
        return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $inp);
    }
    return $inp;
}

function validfilter($value,$regexp,$flags='usi') {
  if (preg_match('/'.$regexp.'/'.$flags, $value,$result) 
       && $result[0]===$value) return $value;  
  else return false;
}
$charset=array(
   'bad'=>'\x00-\x09\x0B\x0C\x0E-\x1F\x80-\xFF',
   'sql'=>'\x00\x0a\x0c\x1a\x22\x27\x5c',
    'en'=>'a-zA-Z',
    'ru'=>'а-яА-ЯёЁ',
   'digits'=>'0-9',
   'num'=>'0-9-.',
   'space'=>'\s',
);
$filter=array(
//login - любой символ, кроме непечатных, sql-"опасных" и пробелов; от 3 до 20 знаков.
 'login'=>"^"."([^".$charset['bad'].$charset['sql'].$charset['space']."]{3,20})$", 
);
//----------------------
$login = validfilter($_REQUEST['login'],$filter['login']);

Then (if necessary), for safe work with the database, we use: mysql_escape_mimic()
In this case, there will be nothing to escape in $login, because we immediately verified this by adding the set $charset['sql'] to the validation filter.
Therefore, $login can be used immediately in a sql query without any concerns.

Answer 4

Prepared Requests .
In general, try to read less habr and more documentation from the developers of the tool you use. Habr and other blog platforms are the subjective opinion of the author. Not a reliable source of information.