Where to use mysql_real_escape_string to prevent SQL Injection?

Question

Hi friends,

I'm in trouble with a group of hackers. they hacked my client's site few times, and my client gets more angry :( my client lost his database (which has hundreds records), and had to enter all :(

now I'm following some more introductions;

• fixed file permissions
• changed ftp and host login info
• cleared all remote mysql accesses

now working on SQL Injection issue. I added mysql_real_escape_string to admin panel login paramaters. So where else should I use this mysql_real_escape_string ? I have few email forms at site, I dont think i need to add there...

I have an index.php as a mainpage. Should I do anything for this page to prevent any sql injection attack via url like index.php?somesql= ?

Please advise me! I appreciate so much!!! :(

---

for example:

I have such code;

public function showDetails($id) {

    // SQL Jobs Details
    $this->sql_job = "SELECT * FROM jobs WHERE id=".mysql_real_escape_string($id);
    $this->rst_job = mysql_query($this->sql_job);           
    $this->row_all = mysql_fetch_assoc($this->rst_job);     

    // SQL State
    $this->sql_state = "SELECT title FROM state WHERE id=" . $this->row_all[$this->tbl_jobs['f4']];
    $this->rst_state = mysql_query($this->sql_state);   
    $this->row_state = mysql_fetch_assoc($this->rst_state);
........

is it enough to use mysql_real_escape_string for $id . not for $this->row_all[$this->tbl_jobs['f4']]

Answer 1

Basically, each time you use some unsafe data (user input, value from a database, a file or an external website, i.e. any data that you are not 100% sure that it is safe) in a SQL query, you should escape it using mysql_real_escape_string. Note that according to OWASP, this function is not secure for escaping dynamic table names (but this is far less common than "basic" user input insertion).

I suggest you to have a look at the whole OWASP article on SQL injection, and also to browse the rest of the website. It's a great source of information about security in web applications.

IMO, the preferred way of preventing SQL injection is to use prepared statements.

Answer 2

One of the golden rules of web development is NEVER (EVER!) trust user input. Therefore, anywhere you have data going into the database, you should call mysql_real_escape_string().

Also, to prevent angry clients in the future, you should regularly backup your database. If I were your client, I would be furious right now.

Good luck in securing your site.

Answer 3

The best way to prevent SQL injection is with use of prepared statements and bind variables. What version of MySQL are you using? Prepared statements are available in 4.1 and higher.

Answer 4

The two biggest things to do with user input are

1. Input Filtering
2. Output Escaping

Input Filtering is the process of transforming the data /[before]/ it's stored in the database. Executing mysql_real_escape_string() falls under this step (although there are better ways to sanitize user data for db insertion), but this step can also include trimming white-space, profanity filtering, markup conversion, and more.

Output Escaping is taking care when send user-content to the browser that you don't allow malicious behavior. This means executing htmlentities() or some other selective screening process.

There are other things you can do, like resource throttling (DOS prevention), form tokens (CSRF protection), etc. Go to OWASP and start reading.