PHP SQL and use of 'WHERE' with a variable

Question

When I used the code below I get the correct result from my database.

$jobnumber= 'Agen912-493';
$sql = 
"
SELECT *
FROM jobcards
WHERE jobnumber='$jobnumber'
";
$result = mysqli_query($conn, $sql);

However when I swap

$jobnumber= 'Agen912-493';

for

$jobnumber= $_GET["jobnumber"];

I get 'no results' from the database. However I know for certain that $_GET["jobnumber"]; is returning the exact same Agen912-493 because I have echoed $jobnumber after setting it equal to $_GET["jobnumber"]; and it returns Agen912-493.

After two hours of head scratching I am at a complete loss to understand whats going wrong here. Simply all I want to do is use the result of a GET[] call (that I have checked is returning the correct string) in the WHERE condition.

Can anyone shed any light on what is going on here please?

how and where is GET coming from? check for errors on the query. we have no idea which API you're using. — Funk Forty Niner
– Funk Forty Niner, Commented Aug 10, 2015 at 2:19
$result = mysqli_query($conn, $sql) or die(mysqli_error($con)); and use error reporting php.net/manual/en/function.error-reporting.php and/or show more relevant code. — Funk Forty Niner
– Funk Forty Niner, Commented Aug 10, 2015 at 2:24
we knew you'd come back @Fred-ii- ... like deer going to the stream — Drew
– Drew, Commented Aug 10, 2015 at 2:53

spencer7593 · Accepted Answer · 2015-08-10 02:59:18Z

2

Your code is vulnerable to SQL Injection. A better pattern is to use prepared statement with bind placeholder.

(EDIT: I misread what OP posted. My bad.)

The most likely explanation for the behavior you are observing is missing single quotes. That's either causing an error (or, MySQL is generously interpreting the literal value in a numeric context, and evaluating to zero.

To see what's happening, echo $sql;

Consider the difference: single quotes make it a string literal:

... FROM t WHERE t.mycol = 'Agen912-493' ^ ^

Without single quotes, MySQL is going to think it's an identifier, and see the dash character as a subtraction operation:

... FROM t WHERE t.mycol = Agen912-493

I expect MySQL is looking for a column name Agen912 and then subtracting 493 from that value.

~~Most likely, MySQL is throwing an error. And~~

Your code isn't checking the return from mysqli_query. It's putting its virtual pinky finger to the corner of its virtual mouth, Dr.Evil-style, and going "I just assume it will all go to plan. What?"

Enable error reporting and check the return from mysqli_query. If there's an error, it will return FALSE rather than a result set.

A better pattern is to use a prepared statement with a bind placeholder:

$sql = "SELECT * FROM jobcards WHERE jobnumber = ?";
$sth = mysqli_prepare($sql);
if (!$sth) {
   // error in prepare
}
else {
   mysqli_stmt_bind_param($sth, 's', $jobnumber);
   mysqli_stmt_execute($sth);
   ...  
}

edited Aug 10, 2015 at 2:59

answered Aug 10, 2015 at 2:46

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

1 Comment

Funk Forty Niner Over a year ago

OP's using WHERE jobnumber='$jobnumber' - I can't see this failing. I'm thinking it's their GET parameter or something else that's failing. As I stated in a comment, if a form is involved and they're using POST, then that could be it. I've asked the OP to post more relevant code, but have gotten no response. However, and as you state; best to escape their data.

Kishan Kikani · Accepted Answer · 2015-08-10 02:47:28Z

0

you can use $_REQUEST[]

$jobnumber= $_REQUEST['jobnumber'];

answered Aug 10, 2015 at 2:47

Kishan Kikani

411 silver badge13 bronze badges

3 Comments

user2755309 Over a year ago

Hi thanks for all the responses. I know my code isn't safe but it's a prototype at this point. So this is the full detail of the issue,,,

user2755309 Over a year ago

Oops. It's late! I am clicking a link on another page. That links goes to dynamic page domain/jobs.php? jobnumber=agen912-493

user2755309 Over a year ago

I then use GET to grab the agen912-493 and assign the outcome to $jobnumber and Echo it just to make sure it's right before passing the output to the Where condition. All Is fine with the echo test. But when i use the variable jobnumber in WHERE I get nothing. But if I set the variable to the same value numerically it works fine!

user3176134 · Accepted Answer · 2015-08-10 07:28:57Z

  Please Create a notepade++ file named it dbconnection.php and copy this code
  <?php 
  mysql_connect('localhost','root','');
  mysql_select_db('database_name');
  ?>
  ----------------------------------------------------------
  Please Create a notepade++ file named it index.php and copy this code

        <!DOCTYPE HTML>
        <html lang="en-US">
        <head>
            <meta charset="UTF-8">
            <title>Your Title</title>
            <meta name = "viewport" content = "width=device-width,initial-scale=1" />
            <link rel="shortcut icon" type = "image/jpg" href="images.jpg" />
            <link rel="stylesheet" type = "text/css" href = "css/style.css" />
            <link rel="stylesheet" type = "text/css" href = "bootstrap/css/bootstrap.css" />
            <link rel="stylesheet" type = "text/css" href = "bootstrap/css/bootstrap.min.css" />
        </head>
        <body>
            <div id = "frmNC_content">
            <div id = "frmNC_header">
            <div id = "frmNC_headertext">Find Contact Form</div>
            </div>
            <div id = "frmNC_body">
                <table width = "100%" border = "0" style = "margin-top:20px;">
                  <form name = "frmEditContact" action = "" method = "post">    
                    <tr>
                        <th width = "15%" style = "text-align:center;">Name : </th>
                        <th width = "30%"><input type = "text" name = "txtName" class = "form-control imput-sm"/></th>
                        <th width = "15%" style = "text-align:center;">Mobile No : </th>
                        <th width = "30%"><input type = "text" name = "txtMobileNo" class = "form-control imput-sm"/></th>
                        <th width = "10%" style = "text-align:center;"><input type = "submit" name = "btnSearch" value = "Search" class = "btn btn-info"/></th>
                    </tr>
                  </form>
                </table>
                <table width = "100%" border = "1" style = "border-collapse:collapse;margin-top:20px;">
                    <tr height = "35px">
                        <th width = "20%" style = "padding-left:10px;background-color:#6495ED;">Name</th>
                        <th width = "20%" style = "padding-left:10px;background-color:#6495ED;">Address</th>
                        <th width = "10%" style = "padding-left:10px;background-color:#6495ED;">Mobile No</th>
                        <th width = "20%" style = "padding-left:10px;background-color:#6495ED;">Office Name</th>
                        <th width = "10%" style = "padding-left:10px;background-color:#6495ED;">Office No</th>
                        <th width = "10%" style = "padding-left:10px;background-color:#6495ED;">Other No</th>
                        <th width = "10%" style = "padding-left:10px;background-color:#6495ED;">Status</th>
                    </tr>
                    <?php 
                    if(isset($_POST['btnSearch']))
                    {
                        if(isset($_POST['txtName']) && $_POST['txtMobileNo']=="")
                        {
                            $name = mysql_real_escape_string($_POST['txtName']);
                            require('dbconnection.php');
                            $sql = "SELECT * FROM `tbl_new_contact` WHERE name = '$name' AND user_code = '$_SESSION[user_code]'";
                            $query = mysql_query($sql);
                            while($row = mysql_fetch_array($query)):
                             echo "<tr height = '25px'>";
                              echo "<td style = 'padding-left:10px;'>".$row['name']."</td>";
                              echo "<td style = 'padding-left:10px;'>".$row['address']."</td>";
                              echo "<td style = 'padding-left:10px;'>".$row['mob_no']."</td>";
                              echo "<td style = 'padding-left:10px;'>".$row['office_name']."</td>";
                              echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[office_number]'>".$row['office_number']."</a></td>";
                              echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[other_mob_no]'>".$row['other_mob_no']."</a></td>";
                              echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[mob_no]'>
                                <button class = 'btn btn-info'><span class='glyphicon glyphicon-phone'></span></button>
                              </a></td>";
                             echo "</tr>";
                            endwhile;
                        }
                        else if(isset($_POST['txtMobileNo']) && $_POST['txtName']=="")
                        {
                            $mobileNo = mysql_real_escape_string($_POST['txtMobileNo']);
                            require('dbconnection.php');
                            $sql = "SELECT * FROM `tbl_new_contact` WHERE mob_no = '$mobileNo' AND user_code = '$_SESSION[user_code]'";
                            $query = mysql_query($sql);
                            while($row = mysql_fetch_array($query)):
                                echo "<tr height = '25px'>";
                              echo "<td style = 'padding-left:10px;'>".$row['name']."</td>";
                              echo "<td style = 'padding-left:10px;'>".$row['address']."</td>";
                              echo "<td style = 'padding-left:10px;'>".$row['mob_no']."</td>";
                              echo "<td style = 'padding-left:10px;'>".$row['office_name']."</td>";
                              echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[office_number]'>".$row['office_number']."</a></td>";
                              echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[other_mob_no]'>".$row['other_mob_no']."</a></td>";
                              echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[mob_no]'>
                                <button class = 'btn btn-info'><span class='glyphicon glyphicon-phone'></span></button>
                              </a></td>";
                             echo "</tr>";
                            endwhile;
                        }
                    }
                    else
                    {
                        require('dbconnection.php');
                        $sql = "SELECT * FROM `tbl_new_contact` WHERE user_code = '$_SESSION[user_code]'";
                        $query = mysql_query($sql);
                        while($row = mysql_fetch_array($query)):
                         echo "<tr height = '25px'>";
                          echo "<td style = 'padding-left:10px;'>".$row['name']."</td>";
                          echo "<td style = 'padding-left:10px;'>".$row['address']."</td>";
                          echo "<td style = 'padding-left:10px;'>".$row['mob_no']."</td>";
                          echo "<td style = 'padding-left:10px;'>".$row['office_name']."</td>";
                          echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[office_number]'>".$row['office_number']."</a></td>";
                          echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[other_mob_no]'>".$row['other_mob_no']."</a></td>";
                          echo "<td style = 'padding-left:10px;'><a href = 'tel:$row[mob_no]'>
                            <button class = 'btn btn-info'><span class='glyphicon glyphicon-phone'></span></button>
                          </a></td>";
                         echo "</tr>";
                        endwhile;
                    }
                    ?>
                </table>
            </div>
            <div id = "frmNC_footer"></div>
            </div>
        </body>
        </html>

Im confused why would you suggest I build a form and database connection to solve the GET parsing problem - it has nothing to do with POSTING or FORMS? I will explain the problem again on another post. But thanks

Collectives™ on Stack Overflow

PHP SQL and use of 'WHERE' with a variable

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related