Unclosed quotation mark after the character string ''

Question

Trying to insert a lot of records from a MySQL database into SQL Server(2005 SQLExpress) database. Here's the query and the error from PHP. I am not understanding it. All opening strings are closed properly but still..

INSERT INTO tbl_email (uniq_id, Respondent_ID, Bcode, BID, Email, Voornaam, Voorletters, Tussenvoegsel, Achternaam, Geslacht, Adres, Huisnummer, Toevoeging, Postcodecijfers, Postcodeletters, Woonplaats, Land, Telefoon1, Mobiel, Telefoon2, Matchkey, Profile, Geboortejaar, Geboortedatum, Leefsituatie, Gezinsgrootte, Inkomen, Beroep, Opleiding, Huis, Huisjaar, Huistype, Tuin, Auto, Beleggen, Kopenopafstand, Financien, Respondenttype, Charitype, Chari, Postcode, Huisdier, EV2, EV3, EV4, EV5, EV6, EV7, EV8, EV9, Aanmaakdatum, fk_ID_projectreactie, status_subscribed, unsubscribeddate, insertdatetime, editdatetime, to_delete) VALUES (6, "41", "288", "53", "[email protected]", "a", "M", "", "0", "2", "0", "176", "", "5652", "EP", "a", "", "", "0", "0", "0", "", "0", "", "2", "2", "", "4", "4", "1", "2006", "", "", "", "1", "1", "", "3", "", "", "a", "1", "", "", "", "", "", "", "", "", "a", 0, 0, Null, Null, Null, 1)
Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark after the character string ''. (severity 15) in crn_export_mssql.php on line 94

What could be the problem. I ran this query singly through SQL Server management console and it accepted and inserted. I even ran this query in another PHP file and data was inserted. However, when I do it in a PHP loop then this problem., The code snippet is,

while(//get rows from mysql)
{ //create query on runtime
$query = $strInsertDump . '('.implode(', ', $arrInsertField).')';
$result = mssql_query($query, $mslink);
}

Edit:

I used PDO now and here's what the errorInfo returns.

Array
(
[0] => HY000
[1] => 20018
[2] => Incorrect syntax near ''. [20018] (severity 5) [(null)]
[3] => -1
[4] => 5
[5] => OpenClient
)

you need to specify single quotes instead of double quotes. did u try that ? — Aravind
That's... quite a few fields. Please reduce to a representative sample statement, one that produces the same message but doesn't have any extraneous fields. — outis
Miss Wijzenbeek from Eindhoven might not like her telephone number being posted like that? — Nanne
INSERT INTO tbl_email (uniq_id, Respondent_ID, Bcode, BID, Email, Voornaam, Voorletters, Tussenvoegsel, Achternaam, Geslacht) VALUES (6, "41", "288", "53", "[email protected]", "Mijntje", "M", "", "Wijzenbeek", "2"); Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark after the character string ''. (severity 15) in crn_export_mssql.php on line 96 The above query is simple but produces the same error. — Khuram

outis outis · Accepted Answer · 2011-06-01T00:42:58

The old mssql extension is outdated. Switch to the SQL Server PHP driver (the PDO version), so you can use prepared statements, which are safer, more performant and won't suffer from any value coding issues, since values are kept separate from the rest of the statement.

$query = $db->prepare('INSERT INTO tbl_email (uniq_id, Respondent_ID, Bcode, ...) 
                       VALUES (:uid, :rid, :bcode, ...)');
while (/* ... */) {
    $query->execute(array(':uid' => ..., ':rid' => ..., ':bcode' => ..., ...));
}

Unclosed quotation mark after the character string ''

2 Answers