php - Update data using PDO why it does not work

363

<?php
$sTable = "mytable";    

$colUpdate = $_GET['field'];// database field name
$valUpdate = $_GET['val']; // the long string ,can be non-English   
$rowID = $_GET['id']; //number

    $pdo = PDO2::getInstance();
    $pdo->exec('SET NAMES utf8'); // for utf-8

    $sql = "UPDATE $sTable
            SET $colUpdate =:valUpdate
            WHERE id =:rowID ";      
    $req = $pdo->prepare($sql);
    $req->bindValue(":valUpdate",  $valUpdate);
    $req->bindValue(":rowID",  $rowID);

    $req->execute();
    $req->closeCursor();        
   ?>

What did I wrong here?Because it works if I did like this:

 <?php
    $sTable = "mytable";
    $pdo = PDO2::getInstance();
    $colUpdate = $_GET['field'];
    $valUpdate = $_GET['val'];      
    $rowID = $_GET['id'];

    $sQuery = " UPDATE $sTable SET  $colUpdate = '$valUpdate' WHERE  id = $rowID";
    $req = $pdo->prepare($sQuery);
    $req->execute();
    $req->closeCursor();    
?>

797

Answer

Solution:

There are several issues in your code:

You are using a Singleton
You aren't checking for errors
You're passing GET variables directly.

Let's address each, shall we?

1. You are using a Singleton

Singletons are evil, they are set in the global space, which makes your application unstable, unreliable and untestable. Besides, what would you do if you needed another database connection?

Solution

Use a new PDO instance.

2. You aren't checking for errors

There aren't any error checking in your code, so if an error does come up, it is silently ignored.

Solution

SetPDO::ATTR_ERRMODE toPDO::ERRMODE_EXCEPTION in the constructor of PDO or usingsetAttribute. It also helps settingPDO::EMULATE_PREPARES tofalse.

3. You're passing GET variables directly into your query

You're passing$colUpdate directly inside your query, even if you are preparing the statement, variables passed directly into the query strings are not escaped.

Solution

Pass it in a placeholder, and bind the value. Also, your structure is most likely flawed if you need user input to determine the column you're updating.

After all of those, I come to the following code:

<?php
/*
 * Variable Initialization
 */
/** @var $table_name string Name of the table to insert */
$table_name = "mytable";

/**
 * @var $field_to_update string Name of field to update
 * @deprecated Should not be used! Database restructuring needed!
 */
$field_to_update = mysql_real_escape_string($_GET['field']); //At least escape it!

/** @var $value_to_insert string */
$value_to_insert = $_GET['val'];

/** @var $row_id integer */
$row_id = $_GET['id'];

$pdo = new PDO("mysql:host=localhost;dbname=database_name", "user", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->exec('SET NAMES utf8'); // for utf-8

$sql = <<<MySQL
UPDATE $table_name
    SET $field_to_update = :valUpdate
    WHERE id = :rowID
MySQL;

$req = $pdo->prepare($sql);
$req->bindValue(":valUpdate", $value_to_insert, PDO::PARAM_STR);
$req->bindValue(":rowID", $row_id, PDO::PARAM_INT);

$req->execute();

People are also looking for solutions to the problem: calling php function in jquery

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question