Home php How to avoid errors and SQL injections in the query?

How to avoid errors and SQL injections in the query?




Here is the processing of data as happens

& lt;? php
include_once 'db.php';
if (isset ($ _ post ['add'])) {
// Transform Special Symbols to Text
$ Name = HTMLSpecialChars ($ _ post ['name']);
$ text = HTMLSpecialChars ($ _ post ['text']);
$ Message = HTMLSpecialChars ($ _ post ['message']);
$ Rating = HTMLSpecialChars ($ _ POST ['Rating']);
// We entered the data from the form to variables and check for errors
$ name = strip_tags (trim ($ _ post ['name']));
$ Text = Strip_Tags (TRIM ($ _ POST ['Text']));
$ message = strip_tags (trim ($ _ post ['message']));
$ Rating = Strip_Tags (Trim ($ _ post ['Rating']));
$ date = $ _post ['date'];
// Enter the date and time of the review
$ DATE = DATE ('Y-M-D H: I');
// Checking data entered
If ($ Name! = '' and $ text! = '' and $ message! = '')
// Sending data in the database
MySQLi_Query ($ Link, "Insert Into Otzivy2 (Name, Text, Message, Date, Rating) Values ​​('$ Name', '$ Text', '$ Message', '$ Date', '$ Rating')");
$ to = 'mail';
$ subject = 'Review';
$ Message = "Review Text:". $ _Post ['Message']. "\ NOTH:". $ _POST ['Text']. "". $ _Post ['email']. "\ Numerous:". date ("D.M.Y - H: I");
$ Headers = Array (
  'From' = & gt; 'mail',
  'Reply-to' = & gt; 'mail',
  'X-male' = & gt; 'PHP /'. phpVersion ()
Mail ($ To, $ Subject, $ Message, $ Headers);
// Close the session
include_once 'clear.php';
include_once 'form.php';
? & gt;

Answer 1, Authority 100%

To avoid any problems with the data transmitted to the request forever, whether it is an error or weight of the site, any data must always be transferred to the database separately from the request . This is done with the help of prepared, or, as they are also called, parameterized requests.

in mysqli to perform a parameterized query, you must perform 4 steps:

  • Replace all variables in a request for special markers, which are called Playtovolders or parameters, and in fact – just question marks
  • Prepare a request for execution using the Prepare () function. This feature accepts a query string and returns an instance of a special MYSQLI_STMT class, with which in the future all manipulations
  • link variables to request using bind_param (). This is a very interesting feature. It accepts all variables as parameters to get into the request, in the same order in which Playtyholders are in the query. But first, first in this function Types for all should be indicated variables , in the form of a string, where the type of variable is denoted by one letter. That is, the letters in this row should be exactly as much as the variables. Fortunately, you can not particularly steam with types and for all variables to indicate the type “S”.
  • Perform a query using Execute (). This feature is performed without parameters

In words, it sounds long, but in practice it turns out not so difficult. But first you need to properly write Database connection code so that there are no problems with Russian letters and that the base reports all errors:

mysqli_report (mysqli_report_strict | mysqli_report_strict);
$ db = new mysqli ('', '1', '1', 'vk2');
$ db- & gt; set_charset ('UTF8MB4');

This code must be written once, preferably in a separate file, and then connect this file to other scripts where you need work with the database. In addition to other useful things, this code reports mysqli, which must be generated by the RNR error every time the query error occurs so that it can be traced and fix it. (Note: To see the errors themselves, it is necessary Set up RNR )

and then you can proceed to perform the request

$ sql = "insert into otzivy2 (name, text, message, date, rating) values ​​(?,? ,?, ?,?) "; 
$ STMT = $ Link- & GT; Prepare ($ SQL);
$ STMT- & gt; bind_param ("SSSSS", $ name, $ text, $ message, $ date, $ rating);
$ STMT- & gt; execute ();

The same concerns and selects select. Only for obtaining a query result, you need to execute another command – get_result ().

For example

$ sql = "select * from otzivy2 limit?"? ";
$ STMT = $ DB- & GT; Prepare ($ SQL);
$ STMT- & gt; bind_param ("ss", $ start, $ num);
$ STMT- & gt; execute ();
$ result = $ STMT- & GT; get_result (); // Get Result

And then as usual Fetch or While

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions