sql query fails with variable

Question

I'm new to PHP and I'm sure I'm missing a basic detail. When I cast this query in SSMS it works fine. It also works if I type it exactly like this in PHP.

But in my code, if I use the variable $barcodevar instead of a static value 'x'. The query stops working. This is my code:

<html>
<input class="input" type="text" name="uid"><br>
<input type="submit" name="submit" value="Submit">    
<?php                              
    if(isset($_POST['submit'])){
    }
        $barcodevar = $_POST['uid'];  
        $serverName = "butterserver";
        $connectionInfo = array("Database"=>"flowers", "UID"=>"buttercup", "PWD"=>"Admin123");
        $conn = sqlsrv_connect( $serverName, $connectionInfo);
        if($conn){
            if($barcodevar == ""){
               echo "field is empty" ;    
            }
            $sql = "SELECT SupplierLotID FROM FactsLot WHERE ID = $barcodevar";
            $stmt = sqlsrv_query( $conn, $sql);
            while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
                echo $row['SupplierLotID']."<br />";
            } 
            sqlsrv_free_stmt( $stmt );
            sqlsrv_close( $conn );
        }
        else{
            echo "no connection";
        }                             
    ?>
</html

After echoing $barcodevar I can see that the value that I type in the "uid" is the same as used in the query of the picture. I can't find any errors so I don't know what the problem is. If there is any more information I should provide please ask, I hope we can solve this.

thanks.

you need quote around the php var WHERE ID = '$barcodevar' for string var ..anyway you should not use php in sql you are at risk for sqlinjection — ScaisEdge, Jan 15 '19 at 12:23
`$barcodevar` needs wrapped in quotes if it's a string. Though given this comes from user input, that's open to SQL injection. — Jonnix, Jan 15 '19 at 12:23
@JonStirling and scaisEdge thank you, that fixed it! I also appreciate your concerns about low security. This is not a productive environment by any means, I'm just trying to understanding how syntax in PHP works. — verfluecht, Jan 15 '19 at 12:30
@MasivuyeCokile Thank you for noticing the duplicate. I'm sorry I asked this. If I knew the problem was in the quotes, I didn't ask, but since I couldn't find any error messages, I didn't know where to search. — verfluecht, Jan 15 '19 at 14:19

score 1 · Accepted Answer · answered Jan 15 '19 at 12:25

$sql = "SELECT SupplierLotID FROM FactsLot WHERE ID = '$barcodevar'";

Just like in your example you tried in SSMS, the ID value is inside single quotes. You have to do the same with your php variable as well.

Keep in mind that your code is really open to sql injection. Try to use prepared statements if you don't want to have any future issues. Read more about it here

score 1 · Answer 2 · edited Jan 15 '19 at 12:31

1

Include the variable $barcodevar between single quotes.

Replace this :

$sql = "SELECT SupplierLotID FROM FactsLot WHERE ID = $barcodevar";

With:

$sql = "SELECT SupplierLotID FROM FactsLot WHERE ID = '$barcodevar'";

edited Jan 15 '19 at 12:31

Masivuye Cokile

4,754
3
19
34

answered Jan 15 '19 at 12:28

Sajjad Ahmed

33
6

sql query fails with variable

2 Answers2