Convertion from mysql to PDO

Question

So i have decided that its time to modernize my code by updating some of my script from mysql to PDO. Ihave used the last days trying to get to know PDO better, but i cant relate the examples that i have found to my script.

Database Connection:

mysql_connect('localhost', 'root', '') or die ('The server is facing issues at the moment');
mysql_select_db('openchat') or die('Problem with connecting to the database');

Php function with db connection included:

function user_exists($username) {
    $username = sanitize($username);
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `user` WHERE `username` = '$username'"), 0) == 1) ? true : false;
}

The function checks if the user already exists, where $username is the posted username in a form, and the function checks if the username is taken or not. I am just showing a small part of the code so i hope this is enough information to get the code :)

Update
I think i finnaly have made an updated version that works!

try {
    $db = new PDO('mysql:host=127.0.0.1;dbname=openchat', 'user', 'user123');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(Exception $e) {
    die('The server is facing issues' . '</br>' . $e->getMessage());
}

function test() {
    global $db;
    $query = $db->query("SELECT COUNT(`user_id`) FROM `user` WHERE `username` = 'testbruker'");
    $result = $query->fetchColumn();

    return ($result == 1) ? true: false;
}

Please, try to do it first. here http://php.net/manual/en/pdo.connections.php you can find how to connect to database with PDO. You also can find many examples of SELECT :) if you fail, we will help you. You don't learn, if you don't try. By the way: it looks like your script is **vulnerable** to sql-injection! — Danielius, Jul 26 '16 at 09:44
@Danielius Just wondering, wouldn't the `sanitise` potentially fix the sql injection? Not that that's good, still pretty unsafe, but it doesn't look like a direct problem. — somethinghere, Jul 26 '16 at 09:53
@somethinghere sorry, i havent seen that function. From old mysql_* functions, as I remember, "mysql_real_escape_string" should help here. http://stackoverflow.com/questions/129677/whats-the-best-method-for-sanitizing-user-input-with-php here i read some more info about sanitizing :) sorry again. + — Danielius, Jul 26 '16 at 10:00
@Danielius Actually, let's not say either are worth looking into as both are old and should be avoided. Just wanted to point out that it's not _all bad_. :) — somethinghere, Jul 26 '16 at 10:01
@Danielius Thanks for the kindnedd! I will try on my own for a while now, and if i cant fix the problem on my own, i will come back! — Rein Bentdal, Jul 26 '16 at 10:18
Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) @Danielius — Jay Blanchard, Jul 26 '16 at 12:34
@JayBlanchard I said, I use PDO now, mysql_* functions should not be used. I never used mysql_real_escape_string, because when i started working with MySQL, I used PDO. I just heard that mysql_real_escape_string helps here :) — Danielius, Jul 26 '16 at 13:05

score 1 · Answer 1 · answered Jul 26 '16 at 10:02

1

You can try something like that. But I recommand you to store your DB information in a safe place. But this should do the work.

try{
    $db = new Database($host,$username,$password,$database);    
    $user = 'This user';
    $sql = "SELECT COUNT (user_id) FROM users WHERE username = ?;";
    $result = $db->prepare($sql);  
    $result ->execute(array($user));
    if ($result ->rowCount() > 0) {
       echo 'The user is present';
    } else {
       echo 'There is nothing';
    }
}
catch (Exception $e){
    die('Error : ' . utf8_encode($e->getMessage()));
}

answered Jul 26 '16 at 10:02

Atnaize

1,766
5
25
54

I actually have the database connection in a seperate file, i just included it here to make it simpler :) – Rein Bentdal Jul 26 '16 at 10:09
One question. What is the $user supposed to mean. The function checks if the user already exists, where $username is the posted username in a form, and the function checks if the username is taken or not – Rein Bentdal Jul 26 '16 at 10:23
@ReinBentdal $user is the variable that content the username you want to check in the DB. $username is your login for the PDO connection. With PDO, each `?` in the query are replaced with the values in the array mentionned in `$result ->execute(array($user));` – Atnaize Jul 26 '16 at 11:35
@Raccoon Is it possible that in database exists more than one user with same username? I am not really good at SQL, but maybe adding `LIMIT 1` would make this run faster? – Danielius Jul 26 '16 at 13:14
@Danielius If you store username in your database you should not have duplicate. Each username must be unique, no ? But if it is not the case, you have to search by ID and not by username. – Atnaize Jul 27 '16 at 12:17
@Raccoon yep I mean that it should be unique. in your code i see that you're searching by username, so I want to say that if username is unique maybe its better to add `LIMIT 1` at end to make code run faster? – Danielius Jul 27 '16 at 12:35
@Danielius If there is only 1 result coming back, then no, LIMIT will not make it any faster. If there are a lot of results, and you only need the first result, and there is no GROUP or ORDER by statements then LIMIT will make it faster. – Atnaize Jul 28 '16 at 08:07
@Raccoon Oh, I just thought that if you have LIMIT 1 all query should stop searching when id is found. And if you don't have, then query searches for more results... I just understood LIMIT 1 effect incorrectly :) – Danielius Jul 28 '16 at 08:11

Convertion from mysql to PDO

1 Answers1