1

I am a new php "developer" and a new member of the SOF site. I am launching a website for the first time and in my online research I have been told that while PHP might be easy at the surface the most important thing that developers overlook is injections.

As SOF states that you should research before you ask a question here, i did and it seems that mysql_real_escape_string(); is required to escape characters that could harm the database. THen I also found that you have prepared statements. However, doing searches on both SOF and Google, I found that it matters less which of the two you use because either way user-inputed data is being used to query/insert into the db.

So I am now really confused because i have found more people championing for escape_string statements and a few for Prepared statements.

This is what I am doing: 

 For Post variables: 

 $thething = mysql_real_escape_string($_POST['field']);


 For Get variables: 

 $thething = mysql_real_escape_string($_GET['id']);


 For Request variables: 

 $thething = mysql_real_escape_string($_REQUEST['id']);

Please do let me know what you guys think.

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Sam Khan
  • 2,417
  • 5
  • 18
  • 16
  • possible duplicate of [How to include a PHP variable inside a mysql insert statement](http://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-insert-statement) – Your Common Sense Oct 28 '11 at 10:37
  • possible duplicate of [Best way to stop SQL Injection in PHP](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php) – Quentin Oct 28 '11 at 10:38
  • @col.shrapnel Speaking of security, there is no difference between both methods, if you correctly bind or format your variables. Binding is just simpler, because it can be used just for any case, while escaping can't (so, you have to cast some variables instead of escaping/quoting). Also, bear in mind that no binding nor escaping can make identifier safe. So, if you have to use a field name or operator in your query, you have to use a value, hardcoded in your script. //// So i was right? it doesn't matter which one you use?? – Sam Khan Oct 28 '11 at 10:42
  • 1
    At least as a new PHP developer you've had the sense to look into Mysql injection protection ! Kudos to you, sir. You don't see this every day. – Tom Oct 28 '11 at 10:42
  • @tom thanks but what good is it if i am confused lol – Sam Khan Oct 28 '11 at 10:44
  • yes, it doesn't matter which to use if you know what are you doing and follow rules. – Your Common Sense Oct 28 '11 at 10:53
  • as a new developer you will rely on escaping strings, the further you proceed you will use a library which help you to avoid injections and further makes your life much easier, like pdo_mysql. Writing this SELECT, INSERT, UPDATE by yourself is a pain in the ass. Trust me :) Maybe in some time you will use a whole framework, which helps you even more. – evildead Oct 28 '11 at 19:27

5 Answers5

1

I find it's easier to remember to always use prepared statements, than to make sure that all parameters are properly escaped. So I recomend prepared statements.

Related questions:

mysqli prepared statements and mysqli_real_escape_string

real_escape_string vs. prepared statements

Community
  • 1
  • 1
socha23
  • 10,171
  • 2
  • 28
  • 25
0

PreparedStatements will escape the input for you, when you use it with placeholder

SELECT * FROM myTable WHERE `id` = ?

However, its not supported by the procedural mysql-extension, what is the reason, why many existing sites stay with mysql_(real_)escape_string(), but you can use it with Mysqli, or PDO. I recommend to use prepared statements, when you are just at the beginning of the development.

KingCrunch
  • 128,817
  • 21
  • 151
  • 173
0

I tend to use mysql_real_escape_string to escape possibly malicious user input.

Prepared statements also achieve the same, so if you find that you might forget to escape user input manually, you could use prepared statements, but after a while of escaping inputs, it becomes second nature.

If you're using a PHP framework such as CakePHP or CodeIgniter, they generally provide functions for the same. For example, CodeIgniter's Active Records take care of all input sanitization for you.

Extra security information:

Since you're new to web development (as am I), also look out for XSS Injections, another point of vulnerability.

Finally, code defensively. This is of much more importance in case of Web development, since your website is accessible to public. In cases where you are validating form data via Javascript, perform validation in your PHP script as well. It might seem redundant, but it is possible to bypass JS (one way is to simply disable JS in the browser).

Salt passwords at the time of hashing, avoid md5 hash. Go for either sha256, sha512 or bcrypt.

With security, it is not a matter of if you will be compromised, but rather, when you will be compromised. Security has to be an on-going, never-ending process.

Community
  • 1
  • 1
Ayush
  • 41,754
  • 51
  • 164
  • 239
  • to let you know, mysql_real_escape_string has nothing to do with user input. – Your Common Sense Oct 28 '11 at 10:49
  • `mysql_real_escape_string` does not have to sanitize user input, but will sanitize any string passed to it as an argument. If you pass a `$_POST` variable which holds the user input, you are sanitizing the user input, aren't you? For instance, in example #2 here http://php.net/manual/en/function.mysql-real-escape-string.php aren't they indirectly sanitizing the user input (which is held in `$_POST['password']`)? – Ayush Oct 28 '11 at 10:53
  • to let you know, mysql_real_escape_string has nothing to do with sanitizing too :) – Your Common Sense Oct 28 '11 at 10:54
  • haha...now you have me baffled. Example #2 in the link I posted in the comment above shows how an unescaped user input for password can get authorized. have a look, and maybe you can shed some light on what I am stumbling on – Ayush Oct 28 '11 at 10:56
  • Sure. This function is not to "sanitize". This function is merely to *escape delimiters*. Thus, no delimiters - no use for the mysql_real_escape_string. add to your example ``" AND area = $_POST['area']"` (with injection value as well) and see. – Your Common Sense Oct 28 '11 at 11:09
0

we thinks that your idea of protection is utterly wrong.

  1. no character could harm the database. But some characters may break a query. Your shouldn't allow this.

  2. thus mysql_real_escape_string(); to escape characters that could break a string.

  3. thus mysql_real_escape_string(); is required to escape strings only. And it won't help a bit with any other part of query.

  4. Also, strings may come into query not only from POST GET and REQUEST but from any source in the world. you must escape a string that going into query, not coming from some source. Destination is a reason, not source.

  5. please refer to the question in the comments for the further explanations

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

I think that this is quite vulnerable to sql injection:

$thething = mysql_real_escape_string($_GET['id']); mysql_query("select * from user where id=".$thething);

Proof of Concept: http://localhost/index.php?id=sleep(30)

This page will take 30 seconds to load because it is vulnerable to sql injection.

A more secure approach to protecting your self from sql injection is parametrized query libraries like PDO or mysqli. You should test everything. I recommend using a service like Sitewatch or an open source project like skipfish.

rook
  • 66,304
  • 38
  • 162
  • 239