(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 — Alive to die - Anant, 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

score 3 · Accepted Answer · edited May 21 '22 at 06:08

3

To avoid this kind of mistakes you need to use prepared statements of PDO (which also prevents SQL INJECTION)

$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

edited May 21 '22 at 06:08

Your Common Sense

156,878
40
214
345

answered Dec 15 '17 at 21:00

Alive to die - Anant

70,531
10
51
98

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
@WilliamPerron not using prepared statements is the ONLY reason this query fails. This answer does address OP's primary concern perfectly. Besides, aside from this petty too localized off topic question form one single person, this answer provided good example for the THOUSAND visited this post afterwards. As it perfectly answers the question that amounts to "Gather specific data from id using PDO" – Your Common Sense May 21 '22 at 06:06

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

1

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

2,174
1
23
33

(PDO) Gather specific data from id

2 Answers2