Random mysql errors but mysql shows different query then send

Question

I have a script that loops trough 2000 client records and copy them in an other database. Sometimes it runs perfectly and sometimes it returns a mysql error with a message that does not corresponds with the query send in PHP. Here is the error message (P A K'2012-12-04 11:05:09' ) O DU LIC TE EY UPDA):

    [0] => Array
        (`enter code here`
        [Level] => Error
        [Code] => 1064
        [Message] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'P   A   K'2012-12-04 11:05:09'
        )
            O  DU LIC TE  EY UPDA' at line 45
        )

The query printed in PHP when the error occures:

    INSERT INTO
                `clients`
            (
                `klantId`,
                `naam`,
                `adres`,
                `postcode`,
                `plaats`,
                `telefoon`,
                `mobiel`,
                `fax`,
                `email`,
                `website`,
                `post_adres`,
                `post_postcode`,
                `post_plaats`,
                `kvknummer`,
                `btwnummer`,
                `rekeningnr`,
                `postbanknr`,
                `mailing`,
                `created`,
                `modified`
            )VALUES
            (
                1134,
                'Naam',
                'Adres 9L',
                '1234 SG',
                'Plaats',
                NULL,
                NULL,
                NULL,
                NULL,
                'www.test.nl',
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                NULL,
                1,
                '2012-12-04 11:05:09',
                '2012-12-04 11:05:09'
            )
            ON DUPLICATE KEY UPDATE
                `naam`=VALUES(`naam`),
                `adres`=VALUES(`adres`),
                `postcode`=VALUES(`postcode`),
                `plaats`=VALUES(`plaats`),
                `telefoon`=VALUES(`telefoon`),
                `mobiel`=VALUES(`mobiel`),
                `fax`=VALUES(`fax`),
                `email`=VALUES(`email`),
                `website`=VALUES(`website`),
                `post_adres`=VALUES(`post_adres`),
                `post_postcode`=VALUES(`post_postcode`),
                `post_plaats`=VALUES(`post_plaats`),
                `kvknummer`=VALUES(`kvknummer`),
                `btwnummer`=VALUES(`btwnummer`),
                `rekeningnr`=VALUES(`rekeningnr`),
                `postbanknr`=VALUES(`postbanknr`),
                `mailing`=VALUES(`mailing`),
                `created`=VALUES(`created`),
                `modified`=VALUES(`modified`)
                ;

My script is running intern in the company and the database runs extern in a datacenter. Script runs 50% right 50% with error...

Thanks in advance!!

Added script with loop:

`
    $dbcon = getDbConnection();
    $aClients = getClients(); // return objects
    foreach ($aClients AS $oClient) {
    $sQuery = ' INSERT INTO
        `clients`
    (
        `klantId`,
        `naam`,
        `adres`,
        `postcode`,
        `plaats`,
        `telefoon`,
        `mobiel`,
        `fax`,
        `email`,
        `website`,
        `post_adres`,
        `post_postcode`,
        `post_plaats`,
        `kvknummer`,
        `btwnummer`,
        `rekeningnr`,
        `postbanknr`,
        `mailing`,
        `created`,
        `modified`
    )VALUES
    (
        ' . $dbcon->real_escape_string($oClient->id) . ',
        ' . $dbcon->real_escape_string($oClient->naam) . ',
        ' . $dbcon->real_escape_string($oClient->adres) . ',
        ' . $dbcon->real_escape_string($oClient->postcode) . ',
        ' . $dbcon->real_escape_string($oClient->plaats) . ',
        ' . $dbcon->real_escape_string($oClient->telefoonnummer) . ',
        ' . $dbcon->real_escape_string($oClient->mobielnummer) . ',
        ' . $dbcon->real_escape_string($oClient->faxnummer) . ',
        ' . $dbcon->real_escape_string($oClient->emailadres) . ',
        ' . $dbcon->real_escape_string($oClient->website) . ',
        ' . $dbcon->real_escape_string($oClient->afwijkend_adres) . ',
        ' . $dbcon->real_escape_string($oClient->afwijkend_postcode) . ',
        ' . $dbcon->real_escape_string($oClient->afwijkend_plaats) . ',
        ' . $dbcon->real_escape_string($oClient->kvknummer) . ',
        ' . $dbcon->real_escape_string($oClient->btwnummer) . ',
        ' . $dbcon->real_escape_string($oClient->bankrekening) . ',
        ' . $dbcon->real_escape_string($oClient->postbank) . ',
        ' . $dbcon->real_escape_string($oClient->mailing == 'ja' ? 1 : 0) . ',
        ' . $dbcon->real_escape_string($oClient->created) . ',
        ' . $dbcon->real_escape_string($oClient->modified) . '
    )
    ON DUPLICATE KEY UPDATE
        `naam`=VALUES(`naam`),
        `adres`=VALUES(`adres`),
        `postcode`=VALUES(`postcode`),
        `plaats`=VALUES(`plaats`),
        `telefoon`=VALUES(`telefoon`),
        `mobiel`=VALUES(`mobiel`),
        `fax`=VALUES(`fax`),
        `email`=VALUES(`email`),
        `website`=VALUES(`website`),
        `post_adres`=VALUES(`post_adres`),
        `post_postcode`=VALUES(`post_postcode`),
        `post_plaats`=VALUES(`post_plaats`),
        `kvknummer`=VALUES(`kvknummer`),
        `btwnummer`=VALUES(`btwnummer`),
        `rekeningnr`=VALUES(`rekeningnr`),
        `postbanknr`=VALUES(`postbanknr`),
        `mailing`=VALUES(`mailing`),
        `created`=VALUES(`created`),
        `modified`=VALUES(`modified`)
    ;';
    $dbcon->query($sQuery);
}`

Last edit: Tried the script on the same server as the database server and then it does not happen. It happens somewhere over the line...

The setting is: script runs on server A and database runs on server B. Not the same network so the internet is used for database connection. Somewhere data get lost...

Is your error message really missing random letters? Are you str_replacing a query at some point? — Mike B
Probably there is some data containing apostrophe - that occurs mainly within names or street names. In that case the query is not properly ended. — shadyyx
This is the exact output. Nothing happens. Just inserting the clients in a foreach loop. — Ajb

ArendE ArendE · Accepted Answer · 2012-12-05T14:25:14

You don't santize the input well. If in PHP you have to make all values valid with mysql_real_escape_string()

'P A K'2012-12-04 11:05:09' looks like you have a ' to many, wich causes the error. Escape this by \'

Querys work, or they don't. No 50-50 change with good coding; so if the query works, there's a problem with some input.

Random mysql errors but mysql shows different query then send

1 Answers