What does it mean to avoid a string?

Question

I read. Do I need to avoid $ _SESSION ['username'] before entering the SQL query? and he said: "You need to avoid every line that you go to sql query, regardless of its origin." Now I know that this is true. A Google search returned over 20,000 results. Only Stackoverflow had 20 pages of results, but no one really explained what shielding is or how to do it. This is just assumed. Can you help me? I want to study because, as always, I am making a web application in PHP.

I looked: Insert Escape characters , What are all escape characters in Java? Cannot delete a row using addcslashes () , Escape character , what does mysql_real_escape_string () really do? How can I avoid double quotes from a string in php? Does MySQL_real_escape_string not add slashes? , remove the escape sequences from a string in php I could continue, but I'm sure you understand. This is not laziness.

Answer 1

Resetting a string means reducing the ambiguity in quotation marks (and other characters) used in that string. For example, when you define a string, you usually surround it in double quotes or single quotes:

"Hello World." 

But what if my string contains double quotes?

 "Hello "World."" 

Now I have an ambiguity - the interpreter does not know where my line ends. If I want to keep my double quotes, I have a couple of options. I could use single quotes around my string:

 'Hello "World."' 

Or I can avoid my quotes:

 "Hello \"World.\"" 

Any quote preceded by a slash is discarded and understood as part of the value of the string.

When it comes to queries, MySQL has certain keywords that it observes that we cannot use in our queries without causing some confusion. Suppose we have a table of values ​​where the column was named “Select,” and we would like to select this:

 SELECT select FROM myTable 

Now we have added some ambiguity to our request. As part of our request, we can reduce this ambiguity by using reverse ticks:

 SELECT `select` FROM myTable 

This eliminates the confusion that we introduced using poor judgment when choosing field names.

Many of them can be handled for you by simply mysql_real_escape_string() your values ​​through mysql_real_escape_string() . In the example below, you can see that we are transmitting user-provided data using this function to ensure that this does not cause any problems for our request:

 // Query $query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'", mysql_real_escape_string($user), mysql_real_escape_string($password)); 

There are other methods for escaping strings, such as add_slashes , addcslashes , quotemeta , etc., although you will find that when the goal is to execute a safe query, by and large, developers prefer mysql_real_escape_string or pg_escape_string (in the context of PostgreSQL.

Answer 2

Some characters have special meaning for the SQL database that you are using. When these characters are used in a query, they can cause unexpected and / or unintended behavior, including allowing an attacker to compromise your database. So that these characters do not affect the query in this way, they must be escaped or, say, differently, the database should say not to treat them as special characters in this query.

In the case of mysql_real_escape_string() it skips \x00 , \n , \r , \ , ' , " and \x1a , because if they are not escaped, they can lead to the former that include SQL injections with the MySQL database.