2

I'm using PHP to connect with a SQL database using sqlsrv drivers. However, I've run into a problem:

$string = "Home";
$DBH->prepare( "INSERT INTO table_name (column_name) VALUES ('" .$string. "')" );
$DBH->execute();

How come this works?

$string = "Home's";
$DBH->prepare( "INSERT INTO table_name (column_name) VALUES ('" .$string. "')" );
$DBH->execute();

But this doesn't?

The SQL database doesn't seem to accept the apostrophe in the $string variable. In the past I would use mysql_real_escape_string but that isn't an option.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
proPhet
  • 1,188
  • 4
  • 16
  • 39
  • 1
    Does and doesn't work are words noone understands here. Find the error. PDO has a quote method http://www.php.net/manual/en/pdo.quote.php – Mike B Jan 16 '14 at 13:10
  • 1
    You're suffering SQL injection - Here's [how to fix it](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Álvaro González Jan 16 '14 at 13:11
  • 1
    "In the past I would use `mysql_real_escape_string` but that isn't an option." This means you know what the problem is. Use prepared statements. – Bart Friederichs Jan 16 '14 at 13:13
  • @ÁlvaroG.Vicario — More then that, that's is the *cause* of the problem, so the question you link to is a duplicate. – Quentin Jan 16 '14 at 13:15

3 Answers3

2

This happens because you're using a prepared statement... but not preparing any input and instead just slapping the value into the SQL. Looking at it should make it clear:

ex 1:

$DBH->prepare( "INSERT INTO table_name (column_name) VALUES ('Home')" );

ex 2:

$DBH->prepare( "INSERT INTO table_name (column_name) VALUES ('Home's')" );

See how your quoting is now messed up?

The proper way to do this is to acutally USE the prepared statement's functionality:

$string = "Home's";
$DBH->prepare( "INSERT INTO table_name (column_name) VALUES (:column_name)" );
$DBH->bindParam(':column_name', $string);
$DBH->execute();

Note how your statement is FIRST prepared with the :column_name parameter ready to accept a value. Then the bindParam puts a value in and executes.

Now can even reuse the prepare with new values:

$string = "Home's Holmes is homing";
$DBH->execute();
Digital Chris
  • 6,177
  • 1
  • 20
  • 29
0

The sqlsrv documentation page has decent examples on how to use prepared statements:

$sql = "INSERT INTO table_name (column_name) VALUES (?)";
$params = array("Home's");    
$stmt = sqlsrv_query( $conn, $sql, $params);
Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195
-1

I think you need to escape the apostrophe by using a double apostrophe. after you do an insert check out the data in the table it should only show one apostrophe. 

$string = "Home''s"; 
$DBH->prepare( "INSERT INTO table_name (column_name) VALUES ('" .$string. "')" );
$DBH->execute();

good luck...

Anthony DeSalvo
  • 9
  • 1