PHP and SQL Server, how to make it secure?

Question

http://www.php.net/manual/en/book.mssql.php

I am using that to connect from a LAMP environment to SQL Server. I noticed I don't have the neet functions like prepared statement and real_escape_string.

How can I make my query as secure as possible? Any help is appreciated.

Don't suggest me to use ODBC or PDO, I don't have that option. I have to run with what I have, and that's MSSQL.

$con = mssql_connect ('xxx', 'xxx', 'xxx');

mssql_select_db('xxx', $con);

$qry = "SELECT 
            firstname
    FROM 
            person
    where firstname = '{$firstname}'";

$query = mssql_query($qry, $con);

@Kerrek: Answer some of his questions.

Lightness Races in Orbit
– Lightness Races in Orbit

2011-07-26 01:40:23 +00:00
Commented Jul 26, 2011 at 1:40 — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Jul 26, 2011 at 1:40

Remus Rusanu · Accepted Answer · 2011-07-26 01:38:51Z

2

Don't use literals, use parameters and bind them to your query:

$con = mssql_connect ('xxx', 'xxx', 'xxx');
mssql_select_db('xxx', $con);
$qry = 'SELECT firstname
     FROM person
    where firstname = @firstname';
mssql_bind ($qry, '@firstname', $firstname, SQLVARCHAR);
$query = mssql_query($qry, $con);

answered Jul 26, 2011 at 1:38

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

1 Comment

vick Over a year ago

according to the docs, "mssql_bind — Adds a parameter to a stored procedure or a remote stored procedure" ...this won't work in my case since it's not a stored procedure.

Lightness Races in Orbit · Accepted Answer · 2011-07-26 01:41:11Z

1

The MSSQL binding supports prepared statements just fine.

The documentation is your friend.

answered Jul 26, 2011 at 1:41

Lightness Races in Orbit

387k77 gold badges670 silver badges1.1k bronze badges

1 Comment

vick Over a year ago

I did read the documentation, I couldn't find it.. mssql_bind is only for stored procedures.

Tarik · Accepted Answer · 2011-07-26 02:30:38Z

You can use htmlentities() to convert html elements into html entities and this function accepts a third argument which is for escaping single and double quotes.

Here is the signature of the function:

string htmlentities ( string $string [, int $flags = ENT_COMPAT [, string $charset [, bool $double_encode = true ]]] )

and the arguments that second parameters may take:

ENT_COMPAT Will convert double-quotes and leave single-quotes alone.

ENT_QUOTES Will convert both double and single quotes.

ENT_NOQUOTES Will leave both double and single quotes unconverted.

ENT_IGNORE Silently discard invalid code unit sequences instead of returning an empty string. Added in PHP 5.3.0. This is provided for backwards compatibility; avoid using it as it may have security implications.

And

You can simply use addslashes() with htmlentities()

and also there is another function with cleans html tags out from the fields which is filter_var () and such example look would be:

$return_value = filter_var($data_to_be_filtered,FILTER_SANITIZE_STRING);

Important

Don't forget to check whether magic_quotes are enabled or not. You can do that by writing :

if(get_magic_quotes_gpc())
    //do something

More about magic_quotes: http://www.php.net/manual/en/info.configuration.php#ini.magic-quotes-gpc

Edit:

You can do more secure transaction by using prepared-statements. They prevent SQL-Injection.

Sample code:

$db = new mysqli();
$db->real_connect($host,$username,$password,$db) or die("Cannot connect");
$query = "select name from users where id = ?";
$st = $db->prepare($query); //faster than normal query run
$st->bind_param("d",$id);
$st->execute();
$st->bind_result($name);
$st->fetch();
echo $name;

Community · Accepted Answer · 2017-05-23 12:04:17Z

0

Mssql doesn't supply a function to escape your query. One option is to use "addslashes()" instead, although it is somewhat ugly (and doesn't encompass everything)

This might be helpful: How to escape strings in SQL Server using PHP?

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Jul 25, 2011 at 23:32

Chris

1,5792 gold badges11 silver badges18 bronze badges

9 Comments

vick Over a year ago

how does that make it more secure?

Chris Over a year ago

It prevents it from SQL injections, which is your biggest fear with SQL queries.

TML Over a year ago

Unfortunately, addslashes() is - at best - half of a solution (as this person has already been told in other help fora). It's pretty trivial to break addslashes().

TML Over a year ago

Sorry, Chris, I was trying to emphasize the point for vick, not disparage your answer. :)

Tarik Over a year ago

Actually there is a function with returns quoted string with slashed. Please check this function which is a native mySQL function :dev.mysql.com/doc/refman/5.0/en/…

|

Collectives™ on Stack Overflow

PHP and SQL Server, how to make it secure?

4 Answers 4

1 Comment

1 Comment

Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related