(PDO) Gather specific data from id

Question

i Have an url stands as : http://website.com/update.php?id={NUMBER}

How would I make PDO grabs the results from that specific id?
Here is my attempt for update.php page :

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=vector",$username,$password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

  $id = $_GET['id'];

    $sql = "SELECT * FROM users WHERE id = '. $id .'";
foreach ($dbh->query($sql) as $row)
    {
    ?>
    <?php echo $row['username']; ?>
    <?php
    }


    $dbh = null;
    }
catch(PDOException $e)
    {
    echo $e->getMessage();
    }
?>

You have to use `pdo prepared statement` as your query is wide-open for SQL INJECTION — Serving Quarantine period, Dec 15 '17 at 20:52
While it's not done the best way, your code should work. Are you getting an error? — Barmar, Dec 15 '17 at 20:54
Isn't `id` a unique key of the table? Why do you need a loop when only one row can be returned? — Barmar, Dec 15 '17 at 20:56
Yeah, it dont work. And I'm not much interested in security at the moment, this is just for personal use and hosted on a local machine. I'm just learning and advancing my skills. — Tibbe USDF, Dec 15 '17 at 20:59
'it don't work' isn't very helpful, have you checked for errors (https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) — Nigel Ren, Dec 15 '17 at 21:04

Serving Quarantine period · Accepted Answer · 2017-12-15T21:11:47.910

2

Note:-

Query $sql = "SELECT * FROM users WHERE id = '. $id .'"; is wrong

It need to be:- $sql = "SELECT * FROM users WHERE id =$id";

But Try to use prepared statements of PDO to prevent from SQL INJECTION

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=vector",$username,$password);

    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // <== add this line

    $id = $_GET['id'];

    $sth = $dbh->prepare("SELECT * FROM users WHERE id = ?");
    $sth->execute(array($id));
    $data = $sth->fetchAll(PDO::FETCH_ASSOC);
    print_r($data); // check  values are coming or not and then try to print it
    $dbh = null;
}catch(PDOException $e){
    echo $e->getMessage();
}

edited Dec 15 '17 at 21:11

answered Dec 15 '17 at 21:00

Serving Quarantine period

66,345
10
43
85

"Use prepared statements" is always a good piece of advice, but I don't it directly solves OP's problem – William Perron Dec 15 '17 at 21:02
@WilliamPerron what do you mean? – Serving Quarantine period Dec 15 '17 at 21:03
OP's question amounts to "*Why does my code doesn't work?*" not "*Is there anything insecure about my code?*". Don't get me wrong, mentionning prepared statements is always a good thing, but you answer doesn't adress OP's primary concern or explain what they have missed/gotten wrong. – William Perron Dec 15 '17 at 21:06

Nigel Ren · Answer 2 · 2017-12-15T21:08:56.580

1

I think your error is that in your SQL, your quotes are a bit mixed up...

$sql = "SELECT * FROM users WHERE id = '. $id .'";

Will most likely give you a SQL statement which will be something like..

SELECT * FROM users WHERE id = '. 1 .'

You should use prepared statements and bind variables as in others have pointed out, but in this case...

$sql = "SELECT * FROM users WHERE id = $id";

Should work.

edited Dec 15 '17 at 21:08

answered Dec 15 '17 at 21:07

Nigel Ren

51,875
11
34
49

Torge · Answer 3 · 2017-12-15T21:07:22.723

0

I assume your ID is a number so don't use = 'id'

also you should use prepared statments to protect against SQL injection attacks

$sql = "SELECT * FROM users WHERE id = :id";
$bind = array( 'id' => $id );
$sth = $dbh->prepare($sql);
$sth->execute($bind);
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    ...
}

edited Dec 15 '17 at 21:07

answered Dec 15 '17 at 21:00

Torge

1,887
1
16
28

(PDO) Gather specific data from id

3 Answers3