properly parameterize mysqli query and print multiple rows

Question

I know this question if floating just about everywhere but I can't seem to apply it to my own problem. I have been using non parameterized queries which makes my project extremely vulnerable to SQL injection

Plot:

I have a form where you enter a date. PHP will get the date and use it in a query to retrieve multiple rows of information.

How do I parametize my mysqli queries and print out the multiple rows? This is what I have so far:

HTML

<form action="#" class="form-horizontal" method="post">
    <label for="fromDate" class="control-label">Date from:</label>
    <input type="date" id="fromDate" class="datepicker" name="searchDate" value="<?php echo $searchDate; ?>">
    <button type="submit" class="btn">Go</button>
    <input type="hidden" name="formIdentifier" value="mainSearch" />
</form>

PHP

if($_POST['formIdentifier'] == "mainSearch"){
    //get date from form
    $searchDate = $_POST['searchDate'];
    $todayDateFrom = $searchDate." 00:00:00";
    $todayDateTo = $searchDate." 23:59:59";
    $stmt = $conn->prepare("SELECT G.* FROM Groups AS G, Customers AS C 
            WHERE C.travel_Date >= '?'
            AND   C.travel_Date <= '?'
            AND C.customer_ID = G.leader_ID");
    $stmt->bind_param("si", todayDateFrom , todayDateTo );
    $stmt->execute();
    /* bind variables to prepared statement */
    $stmt->bind_result($col1, $col2);
}

PHP loop

while ($stmt->fetch()) {
    //Display copious amounts of data.
}

I have based my code from the PHP manual and ended up getting overwhelmed. I'm not even sure if I'm on the right track in preparing my queries let alone attempting how to display the data. Any help would be appreciated!

note - if someone could explain $stmt->bind_param("si", todayDateFrom , todayDateTo ); specifically the "si" that would be fantastic. I just don't understand what its purpose is.

--SOLUTION--

Thanks to all the answers I was able to get my first parameterized query to work.

As suggested, I removed the " ' " that was surrounding " ? " in my query.

I then changed the bind_param "si" to "ss" because i was binding 2 strings, not a string and an integer.

I then binded the results (in this example I have 3 columns in the table)

$stmt->bind_result($group_ID, $leader_ID, $gDate);

Because I don't currently have a 3rd party driver installed, I had to display the results like so:

while ($stmt->fetch()) {
    echo "group_ID: ".$group_ID."<br />";
    echo "leader_ID: ".$leader_ID."<br />";
    echo "group_Date: ".$group_Date."<br />";
}

Shaheer · Accepted Answer · 2014-09-18 06:08:32Z

2

Your existing code works just fine, except you have one tiny mistake, from the manual PHP MySQLi bind_param manual

i corresponding variable has type integer

d corresponding variable has type double

s corresponding variable has type string

So from above we can see that, "i" is used if you want to tell the MySQLi that your parameter is in fact integer, and "s" is used if you want to tell MySQLi that your paramater is in fact an string.

So please replace

$stmt->bind_param("si", todayDateFrom , todayDateTo );

with

$stmt->bind_param("ss", todayDateFrom , todayDateTo );

and remove

'

from the parameter place holders (i.e '?' should be replaced with ?)

And your code of sending the parameterized statement will work just fine

Fetching the results

From the manual PHP MySQLi fetch what fetch does is:

Fetch results from a prepared statement into the bound variables

What you are looking for is get_result. From the manual it:

Gets a result set from a prepared statement

So you can fetch the data using the following code:

$result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM))
    {
        foreach ($row as $r)
        {
            print "$r ";
        }
        print "\n";
    }

Here is the documentation for fetch_array

answered Sep 18, 2014 at 6:08

Shaheer

2,1474 gold badges25 silver badges39 bronze badges

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

4 Comments

Akira Dawson Over a year ago

Thankyou for your comprehensive answer. I have made the required changes but now I get the error "Call to undefined method mysqli_stmt::get_result()" It doesn't like the line "$result = $stmt->get_result();" Apparently a google search tells me I need to install a 3rd party driver in order to use it. Is that the case? Or is there another way I can print out the results?

Akira Dawson Over a year ago

I ended up using $stmt->bind_result and printed it using $stmt->fetch in a while loop

Shaheer Over a year ago

if your problem is solved then gr8! :), otherwise if you have access to php.ini, you can just un-comment the line: extension=php_mysqli_mysqlnd.dll from PHP.ini and restart apache and mysql.

Akira Dawson Over a year ago

I might do this in the future. Thanks heaps again for your answer. Really got me unstuck and now I'm onto my next error!

Community · Accepted Answer · 2023-11-17 19:55:19Z

1

You need to change following line:

$stmt->bind_param("si", todayDateFrom , todayDateTo );

To:

$stmt->bind_param("ss", todayDateFrom , todayDateTo );

Reason: If you look at the following image you can see that i is for Integer and s is for String.

enter image description here

The date parameter should be provided as String, hence use s.

Reference: PHP mysqli_stmt::bind_param documentations.

Also you need to update your SQL statement:

$stmt = $conn->prepare("SELECT G.* FROM Groups AS G, Customers AS C 
        WHERE C.travel_Date >= '?'
        AND   C.travel_Date <= '?'
        AND C.customer_ID = G.leader_ID");

To:

$stmt = $conn->prepare("SELECT G.* FROM Groups AS G, Customers AS C 
        WHERE C.travel_Date >= ?
        AND   C.travel_Date <= ?
        AND C.customer_ID = G.leader_ID");

Reason: Parameter mark should not be surrounded by '.

edited Nov 17, 2023 at 19:55

CommunityBot

11 silver badge

answered Sep 18, 2014 at 5:54

Ambrish

3,6772 gold badges31 silver badges45 bronze badges

1 Comment

Akira Dawson Over a year ago

Thank you for the clarity. I didn't see the specs on the website! But now that i've made the amendments i'm getting a "Number of variables doesn't match number of parameters in prepared statement" does this indicate that I have something wrong in my query now?

Ram Sharma · Accepted Answer · 2014-09-18 06:06:03Z

0

$stmt->bind_param("si", todayDateFrom , todayDateTo );

During param bind you are mentioning that first varible is string and 2nd is integer but as per your code it should be

$stmt->bind_param("ss", $todayDateFrom , $todayDateTo );

Please remove single quotes from your query as well and should be something like this

$stmt = $conn->prepare("SELECT G.* FROM Groups AS G, Customers AS C 
            WHERE C.travel_Date >= ?
            AND C.travel_Date <= ?
            AND C.customer_ID = G.leader_ID");

edited Sep 18, 2014 at 6:06

answered Sep 18, 2014 at 5:50

Ram Sharma

8,8157 gold badges48 silver badges59 bronze badges

4 Comments

Akira Dawson Over a year ago

That makes so much more sense. My problem now that i amended it is " Number of variables doesn't match number of parameters in prepared statement in" which refers to line 48 a.k.a $stmt->bind_param("ss", $todayDateFrom, $todayDateTo);

Ram Sharma Over a year ago

Can you please remove single quotes from your query where ? check my updated answer as well

Akira Dawson Over a year ago

I didn't down vote.. I upvoted as your answer helped me troubleshoot further :)

Ram Sharma Over a year ago

@AkiraDawson, not you but someone down voted me. Thanks, I glad my answer helps you to troubleshoot your issue.

Collectives™ on Stack Overflow

properly parameterize mysqli query and print multiple rows

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related