php MS SQL query with WHERE clause and GET Parameter

Question

<?php
$serverName = "(local)"; //serverName
$connectionInfo = array( "Database"=>"DabaseNew", "UID"=>"sa", "PWD"=>"*****");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn==true ) {
    echo "Connection established.<br />";
}else{
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
} 
$sql = 'SELECT * FROM Dbo.[DATABASE COMPANY SERVICES$Employee] WHERE Email = $_GET['email']';
$stmt = sqlsrv_query( $conn, $sql);
if(!$stmt){
       die( print_r( sqlsrv_errors(), true));
} 
$rows = sqlsrv_has_rows($stmt);
while($obj = sqlsrv_fetch_object( $stmt)){
    echo $obj->Last Name.", ".$obj->First Name."<br />";
}
?>

How do i query for a column/columns where the email is equal to the email entered in a form submited?

score 1 · Answer 1 · answered Jul 02 '14 at 13:32

1

If your form is in post, you should use the $_POST instead of $_GET, if it's in get it's ok. I would personally use post though. You need to filter every input from a user and you have to surround your variable with quotes if it's a string.

if(filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    $email = $_POST['email'];
    $sql = 'SELECT * FROM Dbo.[DATABASE COMPANY SERVICES$Employee] WHERE Email = "'.$email.'"';
}

answered Jul 02 '14 at 13:32

Johnny Dew

971
2
13
29

This is the error i get: Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 1038 [code] => 1038 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. ) ) – user3315848 Jul 03 '14 at 09:30

score 0 · Accepted Answer · edited May 23 '17 at 11:57

To concatenate two strings use . operator like this:

$foo = "Hello ";
$bar = $foo."world!"; // gives "Hello world!"

As you can read within the answer linked within the first edit " double quotes resolve variables inbetween, while ' single quotes don't. your possible solution could be like this:

$query = 'SELECT [First Name] AS firstName, [Last Name] AS lastName
            FROM  Dbo.[DATABASE COMPANY SERVICES$Employee]
            WHERE [Employee Number] = 15 OR [E-Mail] = \''.mssql_escape($mail).'\'';

But you should NEVER directly send a GET parameter top your sql server. Anybody could infiltrate your database or even delete it. Therefore you should add a escape function like this one or consider using another db-library like PDO and build parameterized queries. It might also be sufficient to escape single quotes within the variable with another single quote like this:

function mssql_escape($str) {
    return str_replace("'", "''", $str);
}

score -1 · Answer 3 · answered Jul 02 '14 at 13:27

-1

I dont have much idea about sqlsrv. But your query having syntax error,

You need to concatenate with dot(.) operator while making a dynamic string

$sql = "SELECT * FROM Dbo.[DATABASE COMPANY SERVICES '".$Employee."'] 
       WHERE Email = '".$_GET['email']."'";

answered Jul 02 '14 at 13:27

Ranjith

2,779
3
22
41

Double quotes already resolve variables inbetween - so you are doing that twice in your example. – Daniel Jul 02 '14 at 13:45
He used double quotes and concatenation because there was a clear syntax error at that part. Doing it this way prevents people from making that mistake: `$sql = 'blabla $_GET['email']blabla';` The `'` from the $_GET variable breaks the string... he would have to use concatenation: `$sql = 'blabla ' . $_GET['email'] . ' blabla'`. In fact stackoverflows syntax highlighter even makes this very easy to see in the original post – Merlin Denker Jul 02 '14 at 14:09
The point is, that `$Employee` should not be a variable, but part of the database name. (see last question of owner) So need to use double quotes at all - simply concatenate the mail string. Further using GET parameters in sql queries without escaping is a severe security problem! – Daniel Jul 02 '14 at 14:27

php MS SQL query with WHERE clause and GET Parameter

3 Answers3