Geek Answers Handbook

SQL Injection, Quotes, and PHP

I am completely confused now and would like to know if you could understand everything for me.

After the recent Anon / Lulsec attacks, I asked a question about my php / mysql security.

So, I was thinking how I can protect both PHP and Mysql.

Question: Can someone explain to me what is the best practice to process PHP and Mysql when it comes to quotes?

  • Especially in forms I will need some kind of htmlspecialchars to protect html, right?
  • Can PHP be an exploit at all with a form? Is there any protection?
  • Should I use real_escape_string immediately before the request? Would it be wrong / bad to use it already in PHP (see sanitize_post function)?

I am currently using the following function. The function "sanitizes" all the variables $ _POST and $ _GET. Is it "safe"?

function sanitize_post($array) { global $db; if(is_array($array)) { foreach($array as $key=>$value) { if(is_array($array[$key])) { $array[$key] = sanitize_post($array[$key]); } elseif(is_string($array[$key])) { $array[$key] = $db->real_escape_string(strtr(stripslashes(trim($array[$key])), array("'" => '', '"' => ''))); } } } elseif(is_string($array)) { $array = $db->real_escape_string(strtr(stripslashes(trim($array)), array("'" => '', '"' => ''))); } return $array; }

I am using PHP 5.3.5 with Mysql 5.1.54.

Thanks.

+4
source share
7 answers

There is nothing like "universal sanitation." Let him just quote, because it's all about him.

When quoting, you always quote text for a specific output , for example:

  • string value for mysql query
  • like expression for mysql query
  • html code
  • Json
  • mysql regex
  • php regex

For each case, you need a different quote, because each use is present in a different syntax context. This also implies that quoting should not be done at the input to PHP, but at a specific output ! What is the reason why functions like magic_quotes_gpc are broken (always make sure they are turned off !!!).

So, what methods can be used for citation in these specific cases? (Feel free to correct me, there may be more modern methods, but they work for me)

  • mysql_real_escape_string($str)
  • mysql_real_escape_string(addcslashes($str, "%_"))
  • htmlspecialchars($str)
  • json_encode() - only for utf8! I use my function for iso-8859-2
  • mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case, because the backslash will be reset twice!
  • preg_quote()
+2
source

mysql_real_escape_string deserves your attention.

However, direct inquiry is a quagmire and is no longer considered safe practice. You should read the prepared PDO reports and binding parameters, which have the side effect of quoting, escaping, etc.

+6
source

BEST practice should always use prepared statements. This makes SQL injection impossible. This is done with either PDO or mysqli. Forget all the mysql_ * functions. They are old and outdated.

+6
source

Question: Can someone explain to me what is the best practice for handling PHP and Mysql when it comes to quotes?

It's easy: use prepared statements , e. d. with PDO :: prepare or mysqli_prepare .

+3
source

Do not waste your efforts on using mysql_real_escape_string () or something like that. Just use prepared statements with PDO and SQL injection is not possible.

+2
source

I usually use the PHP functions stripslashes and strip_tags for variables, as they come in via $ _POST (or $ _GET, depending on what you use) and mysql_real_escape_string during the request. (I'm not sure if this is “correct,” but it has worked for me so far.) You can also use the PHP built-in validation filter to check things like email addresses, URLs, data types, etc. PDO is supposedly worthy of preventing SQL injection, but I have not had experience with it yet.

0
source

The main workflow should be

 $data = $_POST['somefield which will go into the database']; ... do data validation ... if (everything ok) { $escaped_data = escape_function($data); $sql = " ... query here with $escaped_data ... "; do_query($sql); }

In principle, data that has been escaped to insert the database should ONLY be used to insert the database. It makes no sense to pre-process everything and rewrite all the data using values ​​shielded with db, when only 2 or 3 of the 50 (say) values ​​actually go anywhere near db.

The same goes for htmlspecialchars. Do not send data through htmlspecialchars if it is not suitable for displaying an HTML type.

Do not store data in a database formatted for a specific purpose, because if you ever need information in a different form for any other purpose, you need to cancel the escaping. Always store raw / unformatted data in db. And note: escaping is done using mysql_real_escape_string (), and the company is not actually stored in db. This is there just to make sure that the data gets into the database SAFE. What is actually stored in db is raw unsecured / unquoted data. After that, it is “safe” in the database.

eg. consider screening functions as handcuffs on a transferred prisoner. While the prisoner is inside the prison, cuffs are not needed.

0
source

More articles:


All Articles