How to do a parameterized query

Question

[ Status: Learner ]

I am attempting to implement a parameterized query but I am having problems. Jonathan Sampson recently hinted at how this could be done (#2286115), but I'm not following his suggestion correctly. Here is my script

$cGrade = "grade" ;

include_once ( "db_login.php" ) ;

$sql = "SELECT   last_name   AS last_name
               , first_name  AS first_name
               , grade       AS gr
               , ethnic      AS eth
               , sex         AS sex
               , student_id  AS id_num
               , reason      AS reason
               , mon_init    AS since
          FROM t_tims0809
         WHERE tag <> '' AND 
               tag IS NOT NULL AND
               schcode = {$schcode}
         ORDER
            BY ('%s') " ;

$qResult = mysql_query ( sprintf ( $sql, $cGrade ) or ( "Error: " . mysql_error() ) ) ;

The query works fine with grade in the ORDER BY phrase.

Thanks.

You want to be careful using this, especially if the order by "parameter" comes from the user. This is subject to a sQL injection because you're doing string replacement not a true parameterized query using safe quoting for the parameter. You'd be much better off using @Xorlev's suggestion and going with prepared statements. — tvanfosson
– tvanfosson, Commented Feb 20, 2010 at 5:16

Xorlev · Accepted Answer · 2010-02-20 05:42:20Z

7

Check out the MySQLi prepared statements class:

$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("sss", $val1, $val2, $val3);

$val1 = 'Stuttgart';
$val2 = 'DEU';
$val3 = 'Baden-Wuerttemberg';

/* Execute the statement */
$stmt->execute();

From the PHP manual.

I feel it's a much superior way of doing parameterized queries, I've switched over to prepared statements when possible, especially during bulk inserts/selects.

edited Feb 20, 2010 at 5:42

answered Feb 20, 2010 at 5:10

Xorlev

8,6533 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dave Over a year ago

Thank you, Xorlev. To tell you the truth, I don't know anything about OOP and when I see the "->" symbol, I run the other way. I'm going to give this a try, however.

Sampson Over a year ago

@Dave Object Oriented Programming is a bit intimidating, but take it little-by-little, and you'll grow to love it :)

Jeffrey Blake · Accepted Answer · 2010-08-10 23:35:05Z

Xorlev's answer is entirely correct. There are other options for syntax too. You can specify the bind variables within the query by name:

$stmt = $mysqli->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

Or if you want to do things shorthand and skip the call to bindParam():

$stmt = $mysqli->prepare('INSERT INTO tbl VALUES(?)');
$stmt->execute($stmt, array("some input"));
$stmt->execute($stmt, array("some other input"));
$stmt->execute($stmt, array("some more input"));

Collectives™ on Stack Overflow

How to do a parameterized query

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related