• 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

    • F'ing FIIINALLY! Much easier to ask people what their hardware is 🙏 and could also very well just say DDR4 or DDR5 without asking to bloat it too much. I'm also curious to see what it will say in More device info...
    • Sure, that must be why it was revealed at the Sony showcase... the game was announced 5 years ago and the visuals are far from poor, what the hell are you smoking, plus the Switch 2 is pretty much on par with an Xbox Series S. It would be absolutely insane for a dev to build a game "with the Switch 2 in mind" when it's coming to all platforms where the majority of sales will happen.
    • The Irony... China wouldn't be what it is today without Apple 😂
    • Microsoft makes it easier to find PC specs in Windows 11 Settings by Taras Buria Windows 11 has already received several improvements that make it easier to learn about your computer's specifications. Recently, Microsoft released Spec Cards for the System > About section, which provide basic information about the PC's main components, such as processor, memory, storage, graphics card, and video memory. Now, the Settings app is getting a new way to find your device info. Microsoft wants to display basic device information right on the Home page of the Settings app. The latest preview builds from the Dev and Beta Channels introduced a new "Your device info" card for the Settings' Home page. It displays specs like processor name and speed, graphics card and the amount of video memory, storage, and RAM. The card also has a link to the "About" section, where you will find more information about your computer, its Windows edition, product ID, and the recently introduced FAQ section that answers common hardware-related questions. The "Your device info" card joins the existing cards on the Settings app's home page. While the section offers useful information like quick access to Bluetooth devices, Wi-Fi, personalization, and recommended settings, users received it with mixed reactions, as many considered it another way for Microsoft to promote its services and subscriptions like Microsoft 365, OneDrive, and Game Pass (seriously, who thinks about Game Pass when opening Settings?). Now, the Settings' Home page is a bit more useful, as it saves you a few clicks when checking your computer's specs. If you want to test the new "Your device info" card, update your PC to build 26200.5622 or newer (Dev Channel). Just keep in mind that Microsoft is rolling it out gradually, and it requires signing in with a Microsoft Account in the United States. Other changes in build 26200.5622 include a new Settings section for Quick Machine Recovery, widget improvements, more app recommendations in the "Open with" dialog, and more. Check out the full release notes here.
  • Recent Achievements

    • Week One Done
      jbatch earned a badge
      Week One Done
    • First Post
      Yianis earned a badge
      First Post
    • Rookie
      GTRoberts went up a rank
      Rookie
    • First Post
      James courage Tabla earned a badge
      First Post
    • Reacting Well
      James courage Tabla earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      397
    2. 2
      +FloatingFatMan
      178
    3. 3
      snowy owl
      170
    4. 4
      ATLien_0
      167
    5. 5
      Xenon
      134
  • Tell a friend

    Love Neowin? Tell a friend!