How to prevent sql injection in a site

Question

I have the following code in my php file:

        session_start();
        include "connect.php";
        if (isset($_POST['email']) && isset($_POST['password'])) {
            $email = htmlspecialchars(mysqli_real_escape_string($conn, $_POST['email']));
            $password = htmlspecialchars(mysqli_real_escape_string($conn, $_POST['password'])); 

        function process() {

            include "connect.php";
            if  (isset($_POST['email']) && isset($_POST['password'])) {
                $email = $_POST["email"];
                $password = $_POST["password"];
            }

            mysqli_select_db($conn, "users");
            $sql = "SELECT * FROM users WHERE email='$email' AND password='$password'";
            $result = mysqli_query($conn, $sql);
            $count = mysqli_num_rows($result);

            if ($count >= 1) { 
            $_SESSION['id'] = $id;
            $_SESSION['email'] = $email;
            $_SESSION['password'] = $password;
            header('location:index.php');
    } else {
        echo "Email/Password is incorrect";
    }
}
        if ($email != "" or $password != "") {
            if (isset($_POST['submit'])) {
                process();
            } else {
                echo "Error: " . mysql_error();
            }

}
}

How would I go about preventing sql injection in my login page? I searched on the internet and most sites said I must use the mysqli_real_escape_string() function, but this did not seem to change things at all when I used the sql injection in my site again. please help :)

http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — John Conde, Jan 03 '14 at 23:15
I recommend Google. https://www.google.com/search?q=preventing+sql&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a — Casey Dwayne, Jan 03 '14 at 23:16

score 1 · Answer 1 · answered Jan 03 '14 at 23:17

1

Yes, use PDO and prepare statements with try/catch blocks. When using prepare, each passes as a secure parameter, eliminating risk of injection.

answered Jan 03 '14 at 23:17

Casey Dwayne

2,142
1
17
32

score 0 · Answer 2 · answered Jan 03 '14 at 23:16

0

Use sql prepare :) http://www.php.net/manual/en/mysqli.prepare.php

From what I know this filters any sql injection

answered Jan 03 '14 at 23:16

brunch875

859
5
15

... only if you use parameters with it as well. – Brad Jan 03 '14 at 23:16

score 0 · Answer 3 · answered Jan 03 '14 at 23:16

0

foreach($_POST as $key => $value) $_POST[$key] = mysqli_real_escape_string($value);

Most simple way, anyway i suggest of use prepare statements

answered Jan 03 '14 at 23:16

Sam

2,950
1
18
26

score 0 · Answer 4 · answered Jan 03 '14 at 23:16

0

First of all, - to avoid sql injection you need to filter any kind of user input. And simplest way to do it, is to use PDO

answered Jan 03 '14 at 23:16

pomaxa

1,740
16
26

score 0 · Answer 5 · answered Jan 03 '14 at 23:33

You need to use prepared statements. I think following code snippet will give you some idea how to use it. please change according to your requirements

  /* Create a prepared statement */

  if($stmt = $mysqli -> prepare("SELECT * FROM users WHERE email=? AND password=?")) {

  /* Bind parameters
     s - string, b - blob, i - int, etc */
  $stmt -> bind_param("ss", $email, $password);

  /* Execute it */
  $stmt -> execute();

 $res = $stmt->get_result();
 $row = $res->fetch_assoc();


  $_SESSION['id'] = $row['id'];
  $_SESSION['email'] = $row['email'];
  $_SESSION['password'] = $row['password'];

  header('location:index.php');
  /* Close statement */
  $stmt -> close();
  }

 /* Close connection */
  $mysqli -> close();

Docs Link: http://www.php.net/manual/en/mysqli.prepare.php

http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

http://forum.codecall.net/topic/44392-php-5-mysqli-prepared-statements/

How to prevent sql injection in a site

5 Answers5