-1

I'm trying to prevent SQL injection using PDO, but I can't seem to connect. This is the working version - not SQL injection safe:

<html>
    <head>
    <title>Insert data into database</title>
    </head>
    <body>
    <?php


    session_start();
    $_SESSION['name'] = $_POST['name'];

    // Connect to database server
    mysql_connect("localhost", "********", "********") or die(mysql_error());

    // Select database
    mysql_select_db("mydatabase") or die(mysql_error());

    // The SQL statement is built

    $strSQL = "INSERT INTO mytable(name) VALUES('" . $_POST["name"] . "')";

    // The SQL statement is executed 
    mysql_query($strSQL) or die (mysql_error());



    // Close the database connection
    mysql_close();

    echo "Your name is " . $_POST["name"] ; 

    ?>

    </body>
    </html>

This is working just fine. I read these pages on how to use PDO to protect against SQL injection attacks:

http://www.w3schools.com/php/php_mysql_connect.asp

http://www.w3schools.com/sql/sql_injection.asp

and wrote the following code following the guideline:

<html>
    <head>
    <title>Insert data into database</title>
    </head>
    <body>
    <?php


    session_start();
    $_SESSION['name'] = $_POST['name'];

    $servername = "localhost";
    $username = "********";
    $password = "********";

    try {
        $conn = new PDO("mysql:host=$servername, dbname=mydatabase", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully";
        }
    catch(PDOException $e)
        {
        echo "Connection failed: " . $e->getMessage();
        }

    echo "You have connected to the database server with PDO"

    // The SQL statement is built
    $stmt = $dbh->prepare("INSERT INTO mytable (name)
    VALUES (:name)");
    $stmt->bindParam(':name', $_POST['name']);
    $stmt->execute();


    // Close the database connection
    mysql_close();

    echo "Your name is " . $_POST["name"] ; 

    ?>

    </body>
    </html>

But this code just gives me a blank page - no error message and nothing inserted into the database.

I also tried doing the connection as described in

http://www.stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

but the result was the same - a blank page without error messages.

What am I doing wrong?

anvandarnamn
  • 133
  • 1
  • 2
  • 8
  • 3
    What does your http servers error log file say? That should _always_ be the first location to look at. That is where php logs errors to. _Read_ the error. Fix the issue. – arkascha Jul 19 '15 at 18:18
  • @PeeHaa Where is the duplicate? I agree that there are some similar ones, such as http://stackoverflow.com/questions/15128468/cant-connect-to-mysql-server-using-pdo , which was closed for being too localised and which obviously was different from my issue. If my answer already has been answered, I'd LOVE to see the answer, but I searched without success. – anvandarnamn Jul 19 '15 at 19:05
  • @arkascha I didn't know there exists such a thing as servers error log file. Where do I find it? – anvandarnamn Jul 19 '15 at 19:05
  • If you get a WSOD (white screen of death) in PHP you have to enable error reporting so that you can see what your mistake is. Hence the dupe. – PeeHaa Jul 19 '15 at 19:08
  • Your code is filled with errors and error reporting will tell you this is the case as well as what in specific is wrong. – PeeHaa Jul 19 '15 at 19:10
  • @anvandarnamn That depends on your operating system and your installation. You can find the exact location inside your host configuration (so as part of your http server configuration). Typically it is something like `/var/log/apache2/` or `/var/log/httpd/` on unixoid systems. – arkascha Jul 19 '15 at 19:20

1 Answers1

1

You're using the wrong variable for $stmt = $dbh->prepare

which should be $conn and not $dbh as per your connection.

Having used error reporting, would have signabled an undefined variable dbh notice/warning.

You also can't use mysql_close(); with PDO as you are mixing APIs, which you can't do.

See Example #3 Closing a connection of "Connections and Connection" in the manual http://php.net/manual/en/pdo.connections.php

Another thing session_start(); is best to be above anything. You may be outputting before header.

Edit: You forgot a semi-colon in this line:

echo "You have connected to the database server with PDO"

which should read as

echo "You have connected to the database server with PDO";

which will break your code.

Error reporting would also have caught that syntax/parse error.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • I have now changed to `$stmt = $conn->prepare` and removed `mysql_close();`. I have moved `session_start();` to above the header and I have added `$conn = null;` at the end as per the example in the link. I appreciate your advice! Nevertheless, I still have the same issue. – anvandarnamn Jul 19 '15 at 18:59
  • @anvandarnamn where you have `echo "Your name is " . $_POST["name"] ;` does it echo anything? – Funk Forty Niner Jul 19 '15 at 19:02
  • @anvandarnamn I also noticed something else, a missing semi-colon in `echo "You have connected to the database server with PDO"` which I added to my answer. I only noticed it now. That will break your code. – Funk Forty Niner Jul 19 '15 at 19:03
  • It's working now. When I added the semicolon after the echo statement as you suggested I got an error message that made me realize that `"mysql:host=$servername;dbname=mydatabase"` was the right way to connect. Thank you for having patience with a messy mind like mine! – anvandarnamn Jul 19 '15 at 19:34
  • @anvandarnamn you're very much welcome, I was glad to have been of help, *cheers* – Funk Forty Niner Jul 19 '15 at 19:36