Security in a Secure Environment

[iwato]

BACKGROUND:  As always I am concerned about security on the one hand, but do not wish to over-code on the other.  In the following outlined procedure the data is fetched from the super secure, vast thicket of layered PHP and sent after translation

 

THE PROCEDURE:

1. Create two MySQL tables linked with a foreign key.
2. Make a method call to the Matomo API and retrieve a four tiered nested array containing two tiers of visitor data.  The first tier consists of an indexed array whose 100 elements each corresponds to a single visit to the Grammar Captive website.  The second tier consists of an associative array, one for each of the 100 elements of the first tier.  The third tier consists of an indexed array of varying length corresponding to one of the elements of each  of the second tier arrays.  Each element of this third tier array contains an associative array  of varying length whose elements correspond to different actions and/or events associated with the respective visit.  The fourth tier contains the data for each recorded action or event.
3. Generate two classes: one for the visit data (2nd tier), and one for the action/event data (4th tier).
4. Translate the keys of the key-value pairs of the two aforementioned tiers so that their values can be properly inserted into either of the two data tables.
5. Insert the translated data into the two data tables via two separate objects created from the two classes.

With the exception of the creation of the data tables Steps 1-5 are all performed within the context of the same PHP document.

QUESTION: Do you see any security risk in the above?

Roddy

[justsomeguy]

Security isn't really an issue in the design of a data structure like an array or class (other than class member visibility).  Security in that involves authentication and authorization, which you haven't mentioned.  Issues like how and when the API and the data are accessible, if other processes running on the same machine can access data they shouldn't be able to, etc.  

[iwato]

Hi, JSG!

Understood.  Thank you. 

Access to neither the local Matomo server, nor the local Grammar Captive database is direct.  The Matomo server is located in one domain and the PHP class documents and  PHP data fetch, translation, and storage code document are located in another domain on the same server.  The Matomo server is accessed using a cURL routine and Matomo authentication key.  The translated data is stored in data tables that are accessed with two MySQLi objects constructed with two separate SQL queries using password authentication.

In brief, both the Matomo API and the GC datatables are accesses with HTTP requests.

Once again, the Matomo database from which the Matomo server fetches its data and the GC database to which the data is ultimately stored are located on the same server.

Roddy

[iwato]

I suppose that the conclusion to this discussion is that anything sent via an HTTP request is vulnerable and that one should protect one's MySQL database from potential threats with the use of prepared statements.  This said, I am confused about their use.

Is it sufficient to prepare and bind a statement only once, and then execute the prepared and bound statement with fresh data as many times as one wants?

Roddy

 

[justsomeguy]

Yeah, that's one way to use them.  Even if you're only going to run it once though, it's still good to use a prepared statement.  If you need to run the same query with different data several times for some reason it's more efficient to prepare it once and then give it the data each time than it is to put the data in the query and run that each time.  MySQL only needs to build the execution plan once.