Proper way of connecting to SQL with PHP and PDO

Question

This is a php file hosted on my web server which connects to a SQL database. The create account function works but I am having issues with getting values out of the table once they are put in.Just for more context I am using the http_get() function in game maker: http://docs.yoyogames.com/source/dadiospice/002_reference/asynchronous%20functions/http_get.html

My SQL table has 4 columns:
P-ID - an auto incrementing primary key
USER - username value
PASS - encrypted password value
SaveString - the most important value which is the string that I am trying to access from the game I have created. This system was somewhat working but I decided to change it to PDO and as a PHP novice

<?php


    $mysql_server = "server";
    $mysql_username = "username";
    $mysql_password = "password";
    $mysql_database = "database";
    $mysql_table = "table"; 


    try{
        $conn = new PDO("mysql:host=$mysql_server;dbname=$mysql_database", $mysql_username, $mysql_password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e){
        echo "Couldn't connect to database";
    }

    $f = $_GET['f']; //function operator
    $puser = $_GET['puser'];
    $pword = $_GET['pword'];
    $pss =  $_GET['pss'];



    $salt = "supersecretsalt"; // Super secret encoder
    $epword = crypt($pword,$salt);


    function create_account($conn, $mysql_table, $puser, $epword, $pss) 
    {
        try{
            $sql = "INSERT INTO $mysql_table (USER, PASS, SaveString) VALUES('$puser','$epword','$pss') ";
            $conn->exec($sql);
        } catch (PDOException $e){
            echo "exception connecting to server";
        }
        $conn = null;
    }
    // This function will save information to an existing account
    function save_info($conn, $mysql_table, $puser, $pword, $pss)
    {   
        try{
            $sql = "UPDATE $mysql_table SET SaveString = $pss  WHERE USER = '$puser'";
            $conn->exec($sql);
        } catch (PDOException $e){
            echo "Save didn't work";
        }
        $conn = null;
    }
    // This function will pull account information
    function load_info($conn, $mysql_table, $puser, $epword)
    {
        try{
            $statement = $conn->prepare("SELECT * FROM $mysql_table WHERE USER = '$puser' AND PASS = '$epword'");
            $statement->execute();
            $row = $statement->fetch();
            echo $row['SaveString'];
        } catch(PDOException $e){
            echo "0";
        }
        $conn = null;
    }


    // This determines which function to call based on the $f parameter passed in the URL.
    switch($f)
    {
        case na: create_account($conn, $mysql_table, $puser, $epword, $pss); break;
        case sv: save_info($conn, $mysql_table, $puser, $epword, $pss); break;
        case ld: load_info($conn, $mysql_table, $puser, $epword); break;
    break;
            default: echo"error";
    }

    ?>

What is happening? Error message? Also note since you switched to PDO you should use prepared statements to avoid injections. http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — chris85, May 31 '15 at 01:17
Yeah, that is a good point. All it does at the moment is say "error.", no other feedback. All I know is it doesn't work. — Aidan, May 31 '15 at 23:51

score 0 · Answer 1 · answered Jun 01 '15 at 01:45

The cases of your switch statement should be in quotes since they are strings. You also had an extra break on your last case. Give this a try,

switch($f) {
     case 'na': 
          create_account($conn, $mysql_table, $puser, $epword, $pss); 
     break;
     case 'sv': 
          save_info($conn, $mysql_table, $puser, $epword, $pss); 
     break;
     case 'ld': 
          load_info($conn, $mysql_table, $puser, $epword); 
     break;
     default: 
          echo "error";
}

Also the case of $f is important if it is NA it will still fail. To make sure it is always lowercase you could use strtolower, http://php.net/strtolower, like this switch(strtolower($f)) {. You also could alternatively put in multiple cases but that seems like a waste, example,

case 'na': 
case 'NA':

With this approach though nA and Na still aren't accounted for.

Proper way of connecting to SQL with PHP and PDO

1 Answers1