prepared-statement

PHP- inserting binary data in mysql using prepared statements

Hello, I have to insert a row using php's mysql improved library to a table in mysql that has its primary key of type VARBINARY. The contents of this field are a computed sha1 hash. If I run the query the old way it works perfectly: $mysqli->$query("INSERT INTO table (id, field1) VALUES (0x" . $id . ",'" . $field1 . "')"); But when I...

Problems with my prepared statement update

$query = "UPDATE kids_entry SET entries=? WHERE parentsemail=?"; $stmt1 = mysqli_prepare($connection, $query); mysqli_stmt_bind_param($stmt1, 'is',$entries,$parentsemail); mysqli_execute($stmt1); if(mysqli_stmt_affected_rows($stmt1) != 1) die("issueasdass"); mysqli_stmt_close($stmt1); The above code does work for me on another page b...

PHP: Injection protection using prepared statements

Hi, I am familiar with using PHP to perform mySQL queries. However, I have been using reg exps as protection against injection attacks. After reading several questions/answers here on SO, I've decided to opt for prepared statements instead. There's two options available (let me know if there are more): mysqli prepared statements PDO ...

Problem calling MySQL stored procedure from php

I have a stored MySQL procedure that takes a customer number and a currency as input. It outputs an array using a select statement. The output array is comprised of Balance and NetBalance, If I call the procedure from MySQL as "call GetAccountBalanceByCurrency(500,'USD')" I get the correct results. However, if I call it from PHP as $...

Are Prepared Statements a waste for normal queries? (PHP)

Nowadays, "Prepared statements" seem to be the only way anyone recommends sending queries to a database. I even see recommendations to use prepared statements for stored procs. However, do to the extra query prepared statements require - and the short time they last - I'm persuaded that they are only useful for a line of INSERT/UPDATE qu...

Does PDO still use binary transfer with prepared statement bound params?

One of the benefits of prepared statements, (really the only major one) is that SQL commands/triggers are sent in one request - and the user-data (or whatever you provide) is sent in another transfer. This transfer of the user-data (i.e. for an INSERT) is supposed to be in binary form so that PHP (and then the DB server) don't waste time...

How do you use memcached with Prepared Statements?

Caching regular SQL queries is simple enough. public function query($sql) { if( $result = cache::get(sha1($sql)) ) { return $result; } $result = $this->connection->query($sql); cache::set(sha1($sql), $result); return $result; } But how do you cache queries with prepared statements since you don't know what t...

Perl: Using DBI placeholders for ORDER BY clause

Can I use placeholders in a prepared statement for the order by clause of my query? I'm guessing not, as I tried it, and it didn't seem to work, but it didn't throw any errors either, which seemed strange. Is there a better way to do this, other than just generating an SQL string with the validated inputs? ...

PreparedStatement not returning ordered ResultSet

I am having some problems and I'm sure it's something stupid. So I have a query like SELECT name, id, xyz FROM table ORDER BY ? then later down the road setting the ? doing a ps.setString(1, "xyz"); I am outputting the query and the value of xyz in the console. When I loop through the ResultSet returned from the PreparedStatemen...

PHP how to check if a MySQLi query needs to be closed?

I created a simple MySQLi class for a project I'm working on to make it easier and more streamlined to pull from and push to the DB. (also to get more familiar with OOP in PHP) A problem I keep running into is in an effort to make it as efficient as possible. I try to close / free every query / statement / result set. In doing so I get ...

How to use prepared statements in this query?

I'm new to PHP and PDO, and I try to use prepared statements here. After 1 hour of trying around I give up. Or my tutorial was just horribly bad. EDIT: This works perfectly without prepared statements: try { $dbh = new PDO('mysql:host=localhost;dbname=test', 'root', 'root'); $prepared = $dbh->prepare('SELECT * from sys_navigat...

Why do these prepared statements not work?

I use NetBeans 6.8 and have MAMP with this config on my mac: Apache 2.0.63 MySQL 5.1.37 PHP 4.4.9 & 5.2.10 APC 3.0.19 & APC 3.1.2 eAccelerator 0.9.5.3 XCache 1.2.2 phpMyAdmin 2.11.9.5 & phpMyAdmin 3.2.0.1 Zend Optimizer 3.3.3 SQLiteManager 1.2.0 Freetype 2.3.9 t1lib 5.1.2 curl 7.19.5 jpeg 7 libpng-1.2.38 gd 2.0.34 libxml 2.7.3 libxslt 1...

Does Python support MySQL prepared statements?

I worked on a PHP project earlier where prepared statements made the SELECT queries 20% faster. I'm wondering if it works on Python? I can't seem to find anything that specifically says it does or does NOT. ...

Good DAL library for PHP which has the similar .Net's feature: command.Parameters.AddWithValue("@demographics", demoXml)

Hi, the question is very straightforward: Good DAL library for PHP which has the similar .Net's feature: command.Parameters.AddWithValue("@demographics", demoXml). mysqli extension is good but I want to have the aforementioned feature too. Putting many "?" does not look nice and rather confusing when a table has many fiends (>= 8). Than...

How to print the actual SQL query to a file?

Say, this is my query $stmt = "SELECT * FROM foo WHERE x=?"; How do I print the above query plus the bound parameter to the screen or to a file in PHP? When I print $stmt it says: mysqli_stmt could not be converted to string Here's my code: if ($stmt = $this->conn->prepare($query)) { $stmt ->bind_param('ss', $un, $pwd); ...

Prepared Statements For ODBC in VB.net

Hi guys, my question is a pretty simple one, however I simply cannot see where i have gone wrong. I just want to know how to create a prepared statement in VB. I know in java one would use ? and these would get replaced. I am aware that in VB you use @ParameterName. Basically my code gets down to where i use the prepare method and the e...

implementing a simple prepared query in PHP

I'm trying to create a very simple database abstraction, one part of it using prepared queries. Now, I have a function take a query string and an array of values like this: $query = "SELECT `first_name`, `last_name` FROM ::table_name WHERE `id` = :id" $values = array( 'table_name' = $this->table_name, 'id' = $user_id, ); this...

Can a prepared statement in MySQL contain PREPARE and EXECUTE?

I wanted to know if prepared statements can be used in the following way: public static function GetCategoryItems($categoryId,$pageNum, &$rnum_pages) { $sql = 'SELECT DISTINCT COUNT(*) AS items_count FROM item I JOIN sub_category SC ON I.sub_category_id = SC.sub_category_id ...

adding parameters to LIMIT clause of a SELECT

Hello all! I have the following prepared statement: $sql = "PREPARE stmt_name FROM 'SELECT I.item_id, I.name , I.price, I.discounted_price, I.thumbnail_photo FROM item I JOIN sub_category SC ON I.sub_category_id = SC.sub_category_id JOIN category C ON C.category_id = SC.category_id WHERE C.category_id =...

Use of PDO in classes

I have a few classes that perform some MySQL queries and prepared statements. However, I am lost in how to incorporate my PDO object within those classes. For example, I want to do something like this: <?php $dbh = new PDO(...); class Foo extends PDO { public $dbh; public function bar() { $this->dbh->prepare('SELECT...