• 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

    • Adobe releases all-new Photoshop app for Android devices by Pradeep Viswanathan Early this year, Adobe released an all-new Photoshop app for iOS devices. Designed from the ground up for smartphones, the app allows users to easily add, remove, adjust, and combine content, as well as access free Adobe Stock assets to create new visuals. Today, Adobe announced a similar, brand-new Photoshop app for Android devices, currently in beta. It’s important to note that this Android version is not intended to replace the desktop version of Photoshop. Instead, it offers access to select powerful Photoshop features—including layering, masking, and the new Generative Fill—within an easy-to-use mobile interface. During the beta phase, the following features are available to all users: Following the beta phase, users will need a new Photoshop Mobile & Web plan to access the premium features. The premium features list includes the ability to remove objects by brushing over them with the Remove Tool, the ability to use Clone Stamp to hide unwanted objects by cloning areas of an image, the ability to fill portions of an image with content sampled from other parts of the image with Content-Aware Fill, the ability to export using additional file formats (PSD, TIF, JPG, PNG), and more. You can download the Adobe Photoshop app from the Google Play Store if your device is running Android 11 or later and has at least 6GB of RAM (8GB or more is recommended for optimal performance).
    • And what about all the toxic waste that "clean" nuclear energy produces?
    • Plasma is beautiful, but my workload is unlikely to ever run on linux ... Cubase 14 with 5 or 6 dozen instances of Kontakt 8. Sigh.
    • Hey everyone! Just curious what your favorite browser is and why? Personally, I prefer using Chrome because it is simple and smooth to use. Would love to hear what everyone else is using and if there's something better I should try!
    • I'm Sam, 20, currently studying IT and passionate about all things tech and music. I found Neowin while browsing for community forums, and I’m excited to be here, connect with others, and maybe even learn something new along the way. Looking forward to chatting with you all!
  • Recent Achievements

    • Conversation Starter
      DarkShrunken earned a badge
      Conversation Starter
    • One Month Later
      jrromero17 earned a badge
      One Month Later
    • Week One Done
      jrromero17 earned a badge
      Week One Done
    • Conversation Starter
      johnwin1 earned a badge
      Conversation Starter
    • One Month Later
      Marwin earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      246
    2. 2
      snowy owl
      156
    3. 3
      ATLien_0
      142
    4. 4
      +FloatingFatMan
      138
    5. 5
      Xenon
      127
  • Tell a friend

    Love Neowin? Tell a friend!