Using the same POST value twice within a PDO PHP script | mySQL

Question

The second time the POST value :supplier is used the script does not write to the database. Without this second instance everything writes as expected. What am I doing wrong?

$hostdb = 'localhost';
$namedb = 'dbname';
$userdb = 'username';
$passdb = 'password';
$charset = 'utf8'; 

if (isset($_POST['name'], $_POST['type'] , $_POST['number'] ,$_POST['supplier']  )) {

    // Connect and create the PDO object
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];

    $conn = new PDO("mysql:host=$hostdb;dbname=$namedb;charset=$charset", $userdb, $passdb, $options);

      try{
        $conn->beginTransaction();

    $stmt = $conn->prepare( ' INSERT INTO `Equipment` (name, type, number, supplier, status, managed_by )
VALUES (:name,:type,:number,:supplier,"Ready", :supplier) ' );


    $stmt->execute([
        'name' => $_POST['name'],
        'type' => $_POST['type'],
        'number' => $_POST['number'],
        'supplier' => $_POST['supplier'],
    ]);

But it is the same exact value as `:supplier` do I somehow set them equal to each other? — John, Jul 17 '19 at 15:50
... or just use unnamed placeholders, `?`. Then it could just be `$stmt->execute([$_POST['name'], $_POST['type'], $_POST['number'], $_POST['supplier'], $_POST['supplier']]);`. — user3783243, Jul 17 '19 at 15:50
If you give it a unique identifier, you can still pass through the same data. `$stmt->execute([..., 'managedBy' => $_POST['supplier']`. — waterloomatt, Jul 17 '19 at 15:52

waterloomatt · Accepted Answer · 2019-07-17T17:38:37.530

1

3 options:

Give the 2nd occurance a unique identifier :managedBy. See example below.
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
Use ? instead of named parameters (as pointed out in the comments)

The 2nd option ($db->setAttribute...) will allow you to use the same identifier multiple times. Taken from https://stackoverflow.com/a/40682033/296555. There are security implications with this method. I would stay clear but leaving it here as an option for future readers.

EDIT

An example of using unique identifiers:

// Notice that we're using a uniquely names placeholder: `:supplier` and `:managedBy`
$stmt = $conn->prepare( ' INSERT INTO `Equipment` (name, type, number, supplier, status, managed_by )
VALUES (:name,:type,:number,:supplier,"Ready", :managedBy) ' );

// Notice that we are referencing those uniquely named placeholders below
// but using the same data `$_POST['supplier']`. 
$stmt->execute([
    'name' => $_POST['name'],
    'type' => $_POST['type'],
    'number' => $_POST['number'],
    'supplier' => $_POST['supplier'],
    'managedBy' => $_POST['supplier'],
]);

edited Jul 17 '19 at 17:38

answered Jul 17 '19 at 15:50

waterloomatt

3,662
1
19
25

in your referenced post the user uses the same variable twice with the option, whay must replace the second one with another variable? – nbk Jul 17 '19 at 15:55
Sorry, I don't follow. – waterloomatt Jul 17 '19 at 15:56
I set `PDO::ATTR_EMULATE_PREPARES => true,` but it is still not working, Is this incorrect? – John Jul 17 '19 at 15:58
Note: If you enable emulation, you should do [a few other things to prevent SQL Injection](https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/12202218#12202218) – GrumpyCrouton Jul 17 '19 at 15:59
True - https://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not. In this case, going to unnamed placeholders or giving all placeholder unique names probably makes the most sense. – waterloomatt Jul 17 '19 at 16:00
your first point is Give the 2nd occurance a unique identifier :managedBy but in the iinked post is written $stmt = $db->prepare("SELECT * FROM table WHERE userid = **:userid** AND userid = **:userid**"); and nothing must be replaced according to the post – nbk Jul 17 '19 at 16:01
@GrumpyCrouton I rather not enable emulation for the security risk, I am little confused how to give a unique identifier – John Jul 17 '19 at 16:01
@PeterDavon I wouldn't enable it either. Honeslty, just switch all of your placeholders such as `:supplier` to `?`, then you don't even have to have the key in your `execute()` array, you just have to pass the variables in the same order as the query. – GrumpyCrouton Jul 17 '19 at 16:04
I just ended up using a different identifier, I appreciate your help – John Jul 17 '19 at 16:08
@nbk - my linked resource is meant for #2 in the list. But as other commenters have pointed out, this is probably not the simplest route to take. – waterloomatt Jul 17 '19 at 17:34
@PeterDavon - I think your issue is resolved but I'll update my answer with an example. – waterloomatt Jul 17 '19 at 17:34
I really appreciate your help, I have one more question that I am struggling with: https://stackoverflow.com/q/57082866/11752827 – John Jul 17 '19 at 19:27

Using the same POST value twice within a PDO PHP script | mySQL

1 Answers1