9

I have a query

$sql ="SELECT CustomerID FROM tblCustomer 
WHERE EmailAddress = '".addslashes($_POST['username']) ."' AND Password = '".addslashes($_POST['password']) ."'";

//  while printing,   it will be

SELECT CustomerID FROM tblCustomer WHERE EmailAddress = 'test@ab\'c.com' AND Password = '123'

if we executing this in a mysql server it works, but not in a sql server

what is the solution for this? . Iam using sql server

Aaron Butacov
  • 32,415
  • 8
  • 47
  • 61
Linto P D
  • 8,839
  • 7
  • 30
  • 39
  • 2
    Could you be clear whether you want to know how this is done in MySQL, or SQL Server? It looks like you really want the latter though your tags say otherwise. – BoltClock Jul 15 '10 at 05:23
  • i wants to work it in SQL Server – Linto P D Jul 15 '10 at 05:41
  • Possible duplicate of [How to escape strings in SQL Server using PHP?](http://stackoverflow.com/questions/574805/how-to-escape-strings-in-sql-server-using-php) – MaxiWheat Jan 26 '16 at 14:26
  • Addslashes was never safe ans has therefore been removed from PHP. You need to find a tutorial or some other source of PHP information that's less than 15 years old. – GordonM Oct 30 '16 at 11:10

4 Answers4

20

addslashes() will escape single quotes with a leading backslash which is valid syntax in MySQL but not in MS SQL Server. The correct way to escape a single quote in MS SQL Server is with another single quote. Use mysql_real_escape_string() for MySQL (mysql_escape_string() has been deprecated). Unfortunately, no analogous mssql_ function exists so you'll have to roll your own using str_replace(), preg_replace() or something similar. Better yet, use a database neutral abstraction layer such as PDO that supports parameterized queries.

Haile
  • 3,120
  • 3
  • 24
  • 40
Asaph
  • 159,146
  • 25
  • 197
  • 199
  • 2
    I'd +1 you, except that you linked to the same halfassed "quoting" function i just downvoted twice. – cHao Jul 15 '10 at 07:14
  • 1
    @cHao: I didn't look carefully enough at that function before I linked to it. I didn't notice it was mangling data. Thanks for pointing that out to me. I've removed the link to the offensive function from my answer. – Asaph Jul 15 '10 at 07:26
  • PHP 7 UPDATE: This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. Alternatives to this function include: mysqli_real_escape_string(), PDO::quote() – Eric Barr Oct 10 '19 at 01:52
7

For MySQL, you want to use mysql_real_escape_string. addslashes does almost the same thing and has fewer letters, but apparently it gets some stuff wrong -- don't use it.

For SQL Server, it's a bit more complicated, as (1) MySQL quotes stuff non-standardly, and (2) i don't see a function made to quote stuff for SQL Server. However, the following should work for you...

$escaped_str = str_replace("'", "''", $unsafe_str);
cHao
  • 84,970
  • 20
  • 145
  • 172
  • 1
    `addslashes()` was designed for a different purpose and gets one subtle case wrong. Don't use it for escaping for MySQL. – staticsan Jul 15 '10 at 06:51
  • @staticsan: I kinda figured there was a reason there was a whole other function (with a deprecated previous version!) to quote MySQL strings. +1 for mentioning it. :) Edited my answer accordingly. – cHao Jul 15 '10 at 06:58
4

for mysql

USE mysql_real_escape_string

http://php.net/manual/en/function.mysql-real-escape-string.php

like :

// Query
$query = sprintf("SELECT * FROM tblCustomer WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

for mssql

look on the answers here :

How to escape strings in SQL Server using PHP?

Community
  • 1
  • 1
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
  • This works only for MySQL, not MS SQL Server. The poster appears to want a solution that works in MS SQL Server too. – Asaph Jul 15 '10 at 05:38
  • if we do like that, the query will output like SELECT * FROM tblCustomer WHERE EmailAddress='' AND Password='' // form values are not outputing from mysql_real_escape_string – Linto P D Jul 15 '10 at 05:39
  • 1
    I wish i could downvote this answer again. Your ms_escape_string function is the kind of stuff i pride myself on **not** doing -- the data should be validated by the caller, and if valid, should make it to the DB **unmodified**. – cHao Jul 15 '10 at 06:15
  • its example i link to the original question. – Haim Evgi Jul 15 '10 at 06:36
  • 1
    @haim evgi: And i downvoted the source of your example too. No quoting function should mangle data -- its whole purpose in life is to see that data makes it into the DB unaffected by the database's quote syntax. – cHao Jul 15 '10 at 06:40
4

You shouldn't really be building the SQL statement dynamically as it's dangerous (and unnecessary). The correct thing to do is to use a paramerised query see http://msdn.microsoft.com/en-us/library/cc296201%28SQL.90%29.aspx

$sql ="SELECT CustomerID FROM tblCustomer WHERE EmailAddress = ? AND Password = ?";
$stmt = sqlsrv_query( $conn, $sql, array($_POST['username'], $_POST['password']));

This is much safer and means you don't have to worry about escaping characters. Another thing is beware of case sensitive / insensitve comparisons. For example if you wanted email address to be case insensitive but password case sensitive use something like:

$sql ="SELECT CustomerID FROM tblCustomer WHERE EmailAddress = ? COLLATE SQL_Latin1_General_CP1_CIAI AND Password = ? COLLATE SQL_Latin1_General_CP1_CSAS";
Joel Mansford
  • 1,306
  • 7
  • 13