Announcement

Collapse
No announcement yet.

MySQL LOAD DATA command?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL LOAD DATA command?

    Hi folks,

    One of my clients is looking to use the LOAD DATA command to import data into tables. Can modules do this using MvQUERY? Should the data file be placed in the Miva data directory, or is some other location required? Anything specific I need to know, such as the format of the path for the INFILE parameter?

    Thanks --
    Kent Multer
    Magic Metal Productions
    http://TheMagicM.com
    * Web developer/designer
    * E-commerce and Miva
    * Author, The Official Miva Web Scripting Book -- available on-line:
    http://www.amazon.com/exec/obidos/IS...icmetalproducA

  • Generally you should not use that at all because it will be very cumbersome on a properly configured server. The issue is that load data can only read files that the user mysql runs as has access to read, and that generally means two things on a properly configured server: 1) A regular user has no ability to place a file somewhere the mysql user can get to it, 2) The mysql user has no access to files in a location the regular user can place them. That being the case, now you're having to log in as root to move files or change ownership just to accomplish a mysql query. Specific to mysql running on linux servers that use 'systemd' to manage processes, further complications may occur because systemd is likely running mysql in a way that its /tmp directory, where you'd typically place a load data file, gets a dynamically generated name with each restart of mysql. Finally, specific to our hosting environment, this would be impossible on shared hosting, and strongly discouraged on dedicated.

    If we're talking about a database used by a Miva Merchant store, nothing outside of the payment application, i.e. Miva Merchant, should be talking to, or altering data in, the database used by that application if following best security practices is of interest.
    David Hubbard
    CIO
    Miva
    [email protected]
    http://www.miva.com

    Comment


    • You what would be a nice thing to have...a "PCI Guide to Database Access and Manipulation". I realize that most experienced developers like Kent, know the basics and can make appropriate assumptions (or even just ask the right questions) but I can imagine less experiance developers not even knowing what to ask.
      Bruce Golub
      Phosphor Media - "Your Success is our Business"

      Improve Your Customer Service | Get MORE Customers | Edit CSS/Javascript/HTML Easily | Make Your Site Faster | Get Indexed by Google | Free Modules | Follow Us on Facebook
      phosphormedia.com

      Comment


      • It's actually pretty simple, because of how very strict PCI is. If you were certifying a "product" for PCI, whether that product be an application, hosting environment, business entity, credit card printing press, etc., such as how we're having our MivaPay product certified, it is not permitted for anything other than "in scope" components and explicitly defined employee roles to talk to something where payment data is stored; encrypted or not. Then, for those permitted employee roles, only if they're performing explicitly defined tasks, such as system administration, backup, application upgrades, etc.

        The reason for this is because other pieces of PCI dictate how logging and auditing occur, and by being strict on what avenues are available to talk to the 'thing' that is storing card data, such as the database sitting behind a payment application, you ideally never get into a situation where a movement of payment data occurs that there's no record of, whether it be authorized or otherwise. If you do have some operational component that is talking to the 'thing' that card data is stored in, that component comes into scope for your PCI certification.

        So, if we're just talking Merchant and the database sitting behind it, the basic rule is simply that one should make all reasonable efforts to find a way to perform a task other than direct SQL queries, phpmyadmin, etc. If there seems to be something that is impossible to accomplish without resorting to direct queries, it certainly wouldn't hurt to run that use case by Brennan to see if it's something that could be solved at some other level, whether it be feature add, API add, command line provisioning system and xml, etc, since those operations would still trigger Merchant's normal syslog events about activity, and it's doing the operations against the database in its normal manner, with security checks, prepared statements, etc.
        David Hubbard
        CIO
        Miva
        [email protected]
        http://www.miva.com

        Comment


        • That's a good encapsulation of what i've always used (cause i aint no data security guru) which is to always piggyback on what miva (or some other PCI compliant component) does. Which brings up another interesting point. What, if any, tracking or scanning is done--especially on shared servers--to look for possible applications that might go outside these norms? For example, some other site developer installs a PHP script to directly access the Merchant database's. And a corollary question, is there enough sandboxing to protect all other Merchant instances on the server from potential harm (assuming their is--but doesn't hurt to ask). Reason for asking of course is there's been a number of people on the forum who've proposed this type of arrangement and even though THEY get told its a bad idea, who knows about the ones that don't ask.
          Bruce Golub
          Phosphor Media - "Your Success is our Business"

          Improve Your Customer Service | Get MORE Customers | Edit CSS/Javascript/HTML Easily | Make Your Site Faster | Get Indexed by Google | Free Modules | Follow Us on Facebook
          phosphormedia.com

          Comment


          • Customers on shared hosting have a no cost option to opt in to having their store database moved to a server and network only accessible to their store. We randomize and replace the database credentials, then re-encrypt them into the configuration for their store. Only their store has access to their store database, it's no longer in Plesk, no more phpmyadmin, any php files that were talking to the same database no longer work once the old copy has been removed, but it wouldn't have current data after the migration regardless.

            That's really the only way to protect around what you're describing, where a malicious entity on the same server (weak customer ssh password, vulnerable php web app, etc) is trying to get into a store database. Mysql's security features are fairly lacking to begin with, but some things it does have aren't compatible with shared hosting. For example, we can't set an IP address lockout for bad auth attempts, because then one misbehaving app could get 'localhost' blocked and take every other store down (we do of course not permit unrestricted remote mysql connections inbound). We can't log all queries, because a shared server copy of MySQL is running anywhere from 100 to 10,000 queries per second. The merchant database config files are protected from one site to the next, so unless the intended victim site has done something naughty, like connecting a copy of wordpress to their store, which would mean credentials for their store are now located in a globally readable php file (so the web server is able to read it), then another site on the server with a compromised app should have no way to determine what mysql user/pass/dbname the intended victim's store is using to try brute forcing it.
            David Hubbard
            CIO
            Miva
            [email protected]
            http://www.miva.com

            Comment


            • Hi guys, thanks for all the info.

              In this case, the data would not be going into the same DB that contains the store's data. This client has a second "exchange database." They put data on products, categories, etc. into the exchange DB; then they trigger a module I wrote that reads the exchange DB and creates or changes store data as needed. Currently, they're using a remote ODBC connection to update the exchange DB, but they're finding that that's too slow. We're looking for a faster way to do that.

              Besides the LOAD DATA command, we have also considered having them prepare the exchange data as a text file containing SQL INSERT commands. They can move the file onto the store's server by FTP; then my module can read and execute the commands. I'm not sure if that will be any faster; but they will want to give it a try after I tell them that LOAD DATA doesn't look like a good way to go. But if anyone knows of a better solution, I'll be glad to look into it.

              Thanks --
              Kent Multer
              Magic Metal Productions
              http://TheMagicM.com
              * Web developer/designer
              * E-commerce and Miva
              * Author, The Official Miva Web Scripting Book -- available on-line:
              http://www.amazon.com/exec/obidos/IS...icmetalproducA

              Comment


              • What's the native format of the data, i.e. what format would you like it to make it to the server in so it requires the least amount of pre-processing by them to convert to something else?
                David Hubbard
                CIO
                Miva
                [email protected]
                http://www.miva.com

                Comment


                • It's mostly typical store data about products, categories, and the client's own "contract" system which is similar to price groups. The data consists mostly of numbers and short alphanumeric codes, with a few longer "memo" text fields.
                  Kent Multer
                  Magic Metal Productions
                  http://TheMagicM.com
                  * Web developer/designer
                  * E-commerce and Miva
                  * Author, The Official Miva Web Scripting Book -- available on-line:
                  http://www.amazon.com/exec/obidos/IS...icmetalproducA

                  Comment


                  • Oh I actually meant how the data is formatted. Since you mentioned ODBC, is whatever is holding this data an actual DBMS of some sort, like MS Access, which means you have flexibility on how a file would be output if you exported in that format instead of ODBC? Just trying to see what the options would be to get the data from format X into mysql columns with the least amount of effort on anyone's part, but that will depend a lot on what's producing the data. If it's something annoying like MS Excel where it's going to put quotes around all your fields, which may themselves contain quotes, or similar, it may require more finessing of the data before we touch it with something on the server side.
                    David Hubbard
                    CIO
                    Miva
                    [email protected]
                    http://www.miva.com

                    Comment


                    • Hi David,

                      I don't know the answers to those questions. I'll give the client a link to this thread, and they or I will be back with more info. Thanks for your help --
                      Kent Multer
                      Magic Metal Productions
                      http://TheMagicM.com
                      * Web developer/designer
                      * E-commerce and Miva
                      * Author, The Official Miva Web Scripting Book -- available on-line:
                      http://www.amazon.com/exec/obidos/IS...icmetalproducA

                      Comment


                      • Hi David,

                        The client's back-end server uses the "Pervasive Database Engine." So they can export data in just about any format. We're looking for something that will provide faster transfers from their server to the exchange DB on the store's server. What do you recommend?

                        Thanks --
                        Kent Multer
                        Magic Metal Productions
                        http://TheMagicM.com
                        * Web developer/designer
                        * E-commerce and Miva
                        * Author, The Official Miva Web Scripting Book -- available on-line:
                        http://www.amazon.com/exec/obidos/IS...icmetalproducA

                        Comment


                        • Hi David, can I get an answer to this question please? My client would like to proceed with this. Thanks --
                          Kent Multer
                          Magic Metal Productions
                          http://TheMagicM.com
                          * Web developer/designer
                          * E-commerce and Miva
                          * Author, The Official Miva Web Scripting Book -- available on-line:
                          http://www.amazon.com/exec/obidos/IS...icmetalproducA

                          Comment


                          • In that case, is it possible to install the native mysql client tools on the server in question?


                            You can write the data into plain text with the relevant field termination, enclosure and line termination characters used, then specified to the 'mysqlimport' command which is a mysql client tool that can read plain text files to import data to the appropriate server just like load data infile. It takes mostly the same options but can be run on a client system instead of requiring the data file be on the server in a directory mysql can read.

                            Docs on that: https://dev.mysql.com/doc/refman/5.7...sqlimport.html

                            Alternatively, another option with the native tools would be write the file out in mysqldump format and import that using the 'mysql' command instead of the 'mysqlimport' command. It will also bypass odbc but won't be quite as fast as mysqlimport.
                            David Hubbard
                            CIO
                            Miva
                            [email protected]
                            http://www.miva.com

                            Comment

                            Working...
                            X