passing parameter to mssql query in php running on apache

[razaqg]

hello,

i am currently having challenges in passing form parameters into an mssql database query from within php.

I'm running running php 5 on apache 2 connecting to mssql server 2008 on windows 7.

the code below is a link from a login page where the form parameter is gotten:

 

<?php

$server = 'myserver1';

 

// Connect to MSSQL

$link = mssql_connect($server, 'user1', 'passwrd1');

 

if (!$link) {

    die('Something went wrong while connecting to MSSQL');

}

 

mssql_select_db('bizinfo_db', $link);

 

$pnlusername = $_POST['pnlusername'];

$pnlpassword = $_POST['pnlpassword'];

 

 

$query = mssql_query('SELECT  Title, Surname, FirstName FROM PeoplesRec where Username = '$pnlusername' ');

 

if (!$result) {

    die('Query failed.');

}

 

// Select every 4th student in the results

for ($i = mssql_num_rows($result) - 1; $i % 4; $i++) {

    if (!mssql_data_seek($result, $i)) {

        continue;

    }

 

    // Fetch the row ...

}

 

  if (!$result) {

die("Database");

}

 

// Free the query result

mssql_free_result($result);

 

 

// Close the link to MSSQL

mssql_close($link);

?>

 

what could be wrong in the parameter assignment as highlighted in red.

Regards.

[ronverdonk]

You are using single quotes there (witin a single quotes statement), so make the statement within double quotes:

$query = mssql_query("SELECT  Title, Surname, FirstName FROM PeoplesRec where Username = '$pnlusername' ");

[razaqg]

Thanks

[shortguy_04]

I'm having a problem with this still...

 

Here is my query:

 

$id_query = "SELECT [Employee ID] FROM Employees WHERE [Employee Code] = '$myvar'";

$id_result = mssql_query($id_query);

 

I keep getting the following warning message:

 

Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark after the character string ''. (severity 15) in C:\mydir\mypage.php on line 57

 

If I remove the $myvar and use static text, it works fine. Any ideas?

[techdude]

Razaqg,

 

Just a warning to you: the code

 

   $pnlusername = $_POST['pnlusername'];
   $pnlpassword = $_POST['pnlpassword'];


$query = mssql_query('SELECT  Title, Surname, FirstName FROM PeoplesRec where Username = '$pnlusername' ');

 

is VERY INSECURE.

 

What would happen if someone submitted

'

union select '1', concat(pnlusername||'-'||pnlpasswd) as name, '1971-01-01', '0' from PeoplesRec;

--

 

or

 

' OR 1=1

 

?

 

The person could execute arbitrary commands to the database! In addition, they could make inserts into the MSSQL users table, and get superaccount access to the database, and later to the server that runs the data base!

 

I would reccommend at the very least url-encoding the values.

 

  $pnlusername = rawurlencode($_POST['pnlusername']);
   $pnlpassword = rawurlencode($_POST['pnlpassword']);


$query = mssql_query('SELECT  Title, Surname, FirstName FROM PeoplesRec where Username = '$pnlusername' ');

 

 

Again, just a warning, but you should learn to ALWAYS VALIDATE USER INPUT. THIS INCLUDES DATA TRANSFERED OVER THE SO-CALLED SECURE HTTPS CHANNEL, OR DATA SUBMITTED BY POST.

 

--

techdude

CompTIA Security+ Certified

[corbin]

urlencode?

 

 

Or, instead you could use a library with bound parameters (better option).

 

Or, you could just replace ' with ''.  (note that that is two ')

[techdude]

I would not reccommend just replacing ' with ", because that can easily be bypassed. However, using an escape function for the appropriate library is a VERY GOOD idea, and should be used at all costs. For MSSQL, try PDO::prepare, or PDO::quote.

 

--

techdude

CompTIA Security+ Certified

[corbin]

It's worth noting that PDO::quote does not work for the ODBC driver.

 

And yes, I should have mentioned that encoding issues and what not can make ' quite dangerous to simple str_replace escape.

 

When working with MSSQL, I always use prepared statements because of the ODBC PDO driver not implementing quote (and I just prefer prepared statements over PDO::quote'ing).