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, Aug 10 '15 at 02:19
`$result = mysqli_query($conn, $sql) or die(mysqli_error($con));` and use error reporting http://php.net/manual/en/function.error-reporting.php and/or show more relevant code. — Funk Forty Niner, Aug 10 '15 at 02:24
we knew you'd come back @Fred-ii- ... like deer going to the stream — Drew, Aug 10 '15 at 02:53
@Drew Yeah, that buck had bigger hoofs than I. He didn't like me going after his harem. — Funk Forty Niner, Aug 10 '15 at 02:57

spencer7593 · Answer 1 · 2015-08-10T02:59:18.033

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);
   ...  
}

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. — Funk Forty Niner, Aug 10 '15 at 02:55

score 0 · Answer 2 · answered Aug 10 '15 at 02:47

0

you can use $_REQUEST[]

$jobnumber= $_REQUEST['jobnumber'];

answered Aug 10 '15 at 02:47

Kishan Kikani

41
1
12

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 Aug 10 '15 at 03:26
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 Aug 10 '15 at 03:33
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! – user2755309 Aug 10 '15 at 03:40

score 0 · Answer 3 · answered Aug 10 '15 at 07:28

  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 — user2755309, Aug 10 '15 at 14:10

PHP SQL and use of 'WHERE' with a variable

3 Answers3

Linked