#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189

    Query error.. Encoding maybe?


    Trying to import a large .sql file, using PHP, since it's too big for phpMyAdmin.

    The error:
    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 'I' at line 1

    The query: Anything imported from the .sql file,
    in this case: INSERT INTO `action` VALUES (1,1,86,'office','item 1',1323154801,0,'active','');

    Now I've been banging my head. It doesn't matter if I'm importing a CREATE TABLE query, or anything else. The error is always "near '{first character of query}' at line 1"

    Not the entire first word, or portion of query. The ascii value of the first character checks out, and I can echo out the query just fine.

    Could there be hidden characters, or "upper" characters I cannot see? What would be a good way to check for this?

    BTW, the sql file came from a linux box, and is being read by a windows apache server.

    If it makes a difference here, this project is using mysql_*
    Last edited by ttremain; February 27th, 2014 at 03:34 PM.
    Thomas Tremain
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    what exactly are you using PHP for? Why don't you simply import the database dump with MySQL itself?

    Anyway, this sounds like a BOM. Remove everything except the first query from the file and then examine the exact bytes. You can do this with a hex editor or with PHP:

    PHP Code:
    <?php

    // get a hex dump of a file
    $hexdump unpack('H*content'file_get_contents('/path/to/import.sql'));
    echo 
    implode(' 'str_split($hexdump['content'], 2));
    Post the result here.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by Jacques1
    Hi,

    what exactly are you using PHP for? Why don't you simply import the database dump with MySQL itself?

    Anyway, this sounds like a BOM. Remove everything except the first query from the file and then examine the exact bytes. You can do this with a hex editor or with PHP:

    Post the result here.
    Thank you for helping me detect that.

    Just as I suspected:

    49 00 4e 00 53 00 45 00 52 00 54 00 20 00 49 00 4e 00 54 00 4f 00 20 00 60 00 61 00 63 00 74 00 69 00 6f 00 6e 00 60 00 20 00 56 00 41 00 4c 00 55 00 45 00 53 00 20 00 28 00 31 00 2c 00 31 00 2c 00 38 00 36 00 2c 00 27 00 6f 00 66 00 66 00 69 00 63 00 65 00 27 00 2c 00 27 00 69 00 74 00 65 00 6d 00 20 00 31 00 27 00 2c 00 31 00 33 00 32 00 33 00 31 00 35 00 34 00 38 00 30 00 31 00 2c 00 30 00 2c 00 27 00 61 00 63 00 74 00 69 00 76 00 65 00 27 00 2c 00 27 00 27 00 29 00 2c 00 28 00 32 00 2c 00 31 00 2c 00 38 00 36 00 2c 00 27 00 6f 00 66 00 66 00 69 00 63 00 65 00 27 00 2c 00 27 00 69 00 74 00 65 00 6d 00 20 00 33 00 27 00 2c 00 31 00 33 00 32 00 33 00 31 00 35 00......etc

    This would also explain why the file doubled in size from one server to the other... (400 MB to 800 mb)

    mysql directly wasn't an option in this case.

    I hate to impose again, but do you have a good suggestion for stripping out the zero bytes?
    Thomas Tremain
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    This is UTF-16 little endian. For some weird reason, the BOM is missing entirey. Where on earth does this dump come from?

    Grab a text editor like good old Notepad or Notepad++ and convert the file into ASCII, UTF-8 or whatever you need.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo