• 0

[PHP] Get actual SQL query from prepared statement


Question

I have the following code:

// $db is a PDO object connecting to a MySQL server using the MySQL Native Driver on PHP 5.3.2 running on Ubuntu
$stmt= $db->prepare("SELECT passhash FROM users WHERE username=:user LIMIT 1");
$stmt->bindParam(":user", $username);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

How do I get the actual SQL query that gets prepared and/or executed (in other words, the result of placing the escaped $username variable into the SQL query text? Obviously I could escape the SQL myself, but that would obviate the benefits of using prepared statements. I looked through the PHP docs and was surprised that this kind of functionality isn't built in; it would be a very useful debug tool.

I'm compiling PHP from source, so I suppose I could theoretically hack such a function in there myself, but that sounds dangerous.

5 answers to this question

Recommended Posts

  • 0

Close, but that just prints out the same text that I put into the prepare() function. I'm guessing this is simply the fundamental nature of prepared statements; maybe the actual SQL doesn't get constructed until it hits the database server.

  • 0
  On 13/05/2010 at 02:34, Hot said:

Hmm, try printing it after you execute then.

I tried it at every step. It's the same each time. Maybe it works with MySQLi; I'll test that. It could be a native driver issue, too, but I'd have to recompile the PHP source tree to get rid of that.
  • 0

Nope, doesn't work with mysqli either. I just get this from doing print_r($stmt):

mysqli_stmt Object
(
    [affected_rows] => -1
    [insert_id] => 0
    [num_rows] => 0
    [param_count] => 1
    [field_count] => 2
    [errno] => 0
    [error] => 
    [sqlstate] => 00000
    [id] => 1
)

With PDO, I get this:

PDOStatement Object
(
    [queryString] => SELECT passhash, badlogincount FROM users WHERE username=:user LIMIT 1
)

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Only if it's the fastest to open ads and blow up my vision with advertising that I don't want to see. Damn Google
    • Chrome is now faster than ever and Google explains how it did it by Sayan Sen Back in June last year, Google touted some great performance improvements for Chrome and shared a blog post explaining in detail how it managed to achieve them. Today, almost exactly a year later, the search giant is back again with another such post as it continues to make performance gains in its Chromium browser. Interestingly, Google is not the only one to make such claims in recent times. Microsoft also highlighted recently how Edge was getting significantly faster. Both Microsoft and Google have cited the Speedometer 3.0 benchmark to test. We recently measured browsing performance during our T-Force DDR5-7200 RAM review, also using Speedometer 3.0. In its blog post, Google says that the development team made significant improvements to memory management and caching. This includes some redesigning effort of the memory layouts for many internal data structures used in components such as DOM, CSS, layout, and painting. Google says that Blink, the rendering engine in Chromium, now "avoids a lot of useless churn" so as to make better use of the CPU caches. In the areas where memory handling previously relied on garbage collection in Oilpan, like the DOM (document object model), the team has expanded that by shifting from using malloc (memory allocation function) to Oilpan entirely. For those wondering, Olipan is the garbage collector in Blink. Some of the memory management and caching improvements Google made are fundamental to good code optimization. If you recall, recently, a senior Microsoft engineer also pointed out many of these issues in apps that slow Windows down. There are also improvements in handling strings within the renderer; the hashing method was updated to rapidhash, which is said to improve performance. For when rendering tasks become inherently expensive, such as computing CSS styles for various elements, Google adds that caching techniques have been enhanced to achieve higher cache hits and fewer misses.
    • Laptop users, this appears to be single-sided so it should fit even in cases with thin slots.
    • Apple wouldn't be what it is today without China either...
  • Recent Achievements

    • First Post
      Uranus_enjoyer earned a badge
      First Post
    • Week One Done
      Uranus_enjoyer earned a badge
      Week One Done
    • Week One Done
      jfam earned a badge
      Week One Done
    • First Post
      survivor303 earned a badge
      First Post
    • Week One Done
      CHUNWEI earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      428
    2. 2
      +FloatingFatMan
      196
    3. 3
      snowy owl
      191
    4. 4
      ATLien_0
      186
    5. 5
      Xenon
      141
  • Tell a friend

    Love Neowin? Tell a friend!