MSSQL and allowing apostrophe to be inserted in string via a form

PHP
1

Hi,

I have a form and on some occassions the user will need to have an apostrophe in the data being uploaded to the database.

This is the MySQL way:


$result=mysql_query("INSERT INTO $table wf_FirstName1,wf_LastName3) VALUES ('".mysql_real_escape_string($wf_FirstName1)."', '".mysql_real_escape_string($wf_LastName3)."')";

So wondered what would be the approach when using MSSQL as below:


$queryInsert="INSERT INTO Country (Country_Name)".
"VALUES('$cName')";
2

Have you considered migrating over to using PDO and [URL=“http://www.php.net/manual/en/pdo.prepared-statements.php”]prepared statements?

3

Hi,

Yes I have been advised about it before, but as I’m on my own doing this project, have a tight deadline, and learning on the go, I will have to come back to it later.

Its an intranet project, so its not as vulnerable as it could be.

To be honest, I wish I had someone else here in the office a little bit more senior than me to advise on things like that, as I’m learning PHP & MSSQL from scratch on my own, and this forum and google have been my main forms of working through it.

Thanks for getting back to me though

4

This seems to work, its not causing an error, and its uploads to the database fine too.


$cName=str_replace("'", "'", $_POST['countryName']);
5

You can also utilize addslashes

6

pretty sure that\'s not gonna work in mssql

as far as i know, only mysql allows quotes to be escaped with a backslash

the standard sql method is to use two single quotes in succession –

INSERT INTO surnames VALUES ( 'O''Toole' )
7

Ah, yes. That is true, there is a comment on the page that would also work (granted multichild, your solution is just fine).

[Editor’s note: See also the php.ini configuration magic_quotes_sybase at the URL http://www.php.net/manual/en/ref.sybase.php]

please note that addslashes will NOT work with mssql, since mssql does not use the backslash character as an escape mechanism. just double your quotes instead. or use this:

<?php 
function mssql_addslashes($data) { 
    $data = str_replace("'", "''", $data); 
    return $data; 
} 
?>
8

Great stuff,

Cheers guy’s, getting the thumbs up from you lot is as good as it gets.

9

On the same subject, I have an option to enter a contract value, at the moment i asked if they could simply put a row of numbers in (12500), but they now want to be able to put a comma in and a full stop (12,500.50), so to allow this does the following make sense:


$cValue=str_replace("'"/","/".", "&#039;"/"&#44;"/"&#46;", $_POST['contractValue']);

I tried it and it didnt work, so I assume its either because of the way I have tried to highlight what needs to change, or its a mathematical thing.

10

Tried this which resulted in white out, so that didnt work;


$cValue=str_replace(str_replace(str_replace("'", "&#039;"),",","&#44;"),".","&#46;"), $_POST['contractValue']);
11

You were closer with your first attempt, the following should work

$cValue=str_replace(array("'",",","."), array("&amp;#039;","&amp;#44;","&amp;#46;"), $_POST['contractValue']);
12

For whatever reason, I can’t get the & to be an & in the second array, so the second array should be
array(“'”,“,”,“.”)

13

Well you know what Im going to say cpradio.

That worked…

Thank you once again…

14

Hi,

Yes I thought I had to change those to HTML:


$cValue=str_replace(array("'",",","."), array("&#039;","&#44;","&#46;"), $_POST['contractValue']);

And it worked prefectly.

15

Working out all that probably took more time than using prepared statements. And there are still lots of cases where one could knock MSSQL off its kilter here.