1

Speaking about SQL Injection.

Right now, I do the following for my website.

  1. Sanitize (any) parameters in query with mysql_real_escape_string().
  2. Integer data goes intval().
  3. Query with an user which has ALL PREVILIGIES.

Question 1: Does it prevent SQL Injection alone?

Also, I'm thinking about doing following.

Instead of using the query with all previligied user, I want to create an user for each task, like update, delete, select, drop etc.

When doing query, I can select which user to use like

$database->selectUser('SELECT')->runQuery('query');

Question 2: Would this help me on security? Does it worth doing it?

Chris Olsson
  • 43
  • 4
  • 2
    The answer is always the same: don't use `mysql_*` functions, they're being deprecated. Use prepared statements, consistently, and you will not have any injection problems. – Oliver Charlesworth Jan 13 '13 at 00:16
  • 3
    [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). – samayo Jan 13 '13 at 00:16
  • I'll have a look at PDO, but my second question still stands. :) – Chris Olsson Jan 13 '13 at 00:20
  • 1
    It can help with security since you are running the task with a user that can only do a specific task. So say if there is a hole by accident somewhere in your SQL code for say a SELECT statement, that query can't be used to DROP. It's good practice to do so, but many people don't. Check out the [OWASP guidelines](https://www.owasp.org/images/5/56/OWASP_Testing_Guide_v3.pdf) and [Least Privilege](https://www.owasp.org/index.php/Least_privilege) – kittycat Jan 13 '13 at 00:22
  • Okay, mysqli seems so familiar to mysql. Thing is, what is the alternative to mysql_real_escape_string() in MYSQLI or PDO? – Chris Olsson Jan 13 '13 at 00:23
  • @ChrisOlsson you would use [prepared statements](http://php.net/manual/en/pdo.prepare.php), which would not require you to pre-escape, but make sure to bind your variables. – kittycat Jan 13 '13 at 00:26
  • I already use prepared statements in my queries. Wrote a database class for this purpose. Like; $this->query('UPDATE USERS SET Username = ?', $username) The class automatically replaces ? with $username and sanitizes with mysql_real_escape_string(). I don't know if mysql_real_escape_string() alone is enough to prevent injection, though! – Chris Olsson Jan 13 '13 at 00:29
  • @ChrisOlsson, don't use mysql_* functions as they are deprecated. – kittycat Jan 13 '13 at 00:30
  • @ChrisOlsson you seem to be asking security related questions. I highly suggest you check out the OWASP links in my above comment so you can learn more about web application security, it's the best place to go to do so. – kittycat Jan 13 '13 at 00:37
  • @crypticツ Yeah, people told it already, but does it secure sql injection alone for today's websites? I have some websites I coded like years ago. I just wonder if mysql_real_escape_string() makes them safe "today". – Chris Olsson Jan 13 '13 at 00:37
  • @ChrisOlsson Yeah it would, but once they drop the MySQL extension your code will all break in newer versions of PHP (5.5+), which you of course would need to keep up to date for security reasons. So you should modify your database wrapper to use PDO as your code will all still work since they use the common database class you made. – kittycat Jan 13 '13 at 00:43
  • Can you have a look at my class? (http://pastebin.com/aneq76un) Also, which one would you suggest according to my class, PDO or mysqli? Ps. Yeah, updating my database class will be enough so I'm pretty lucky. :) – Chris Olsson Jan 13 '13 at 00:48

1 Answers1

-1

Question 1: In general, mysql_real_escape_string will stop the basic SQL injections that will be attempted against you, but there ARE exceptions. See this question.

You should really switch all your code over to Mysqli or PDO.

Question 2: That really does not help your security. Let's say you start seeing weird stuff happening in your databases and you think you have been hacked...but which USER is it? If you think a password has been cracked, you will have to go through and change ALL your passwords.

Besides that, you basically have to define a new connection with every account....

Make ONE master account and give it a really good password. If strange stuff starts happening, you only have one password to change.

Community
  • 1
  • 1
kmoney12
  • 4,413
  • 5
  • 37
  • 59
  • -1 because having to change multiple passwords is not a reason to avoid more secure approaches. Granting privileges on a per-need basis is better than using one admin-level user. Besides, why are attackers allowed to authenticate directly with your database anyways? – Waleed Khan Jan 13 '13 at 02:37