PHP - MySQL experts ?

[User]

 

[mjr]

Yes. I think kml is text so you could just use a text column, or if not, there's always blob. https://dev.mysql.com/doc/refman/5.0/en/blob.html

 

[Octet]

Yep, as mjray has stated there are a couple of datatypes that you could use, just make sure you use parametised statements to mitigate against injection attacks.

You might however find it better to save it on the server, and then simply store it's location in the database. It shall depend on the size of the file mainly, but it might be faster and less expensive in terms of IO operations.

 

[jefmcg]

[QUOTE 3322120, member: 9609"]hmmmm. trouble is I am not really sure what I want to do and everybody talks in another language. I may need some time[/QUOTE]
Yes.

The trick is to ask questions about the problem, not the solution. Say what you are trying to achieve, and you can get better advice on how to get there. E.g. "I want users to be able to download KML files of the tracks they create" rather than "How do I store a KML file in the database?"

FWIW, I had an old database of GPS tracking from my windows 6 smart phone dating back to 2008. I decided to get that into strava. The table format was latitude, longitude, date/time and trip number**. So I wrote a script to get all the points for each trip and create a GPX file from them. It was pretty quick to do. So rather than storing entire KML, storing the points and then generating gpx, kml, tcx or whatever on demand might be better.


*** and lots of other things I didn't need.

 

[John the Monkey]

You might however find it better to save it on the server, and then simply store it's location in the database. It shall depend on the size of the file mainly, but it might be faster and less expensive in terms of IO operations.

Been a while since I did anything like this (and it was in Oracle, not MySQL) but I favour storing file location, rather than files (the files live on the filesystem) - much more flexible, ime.

 

[Octet]

Been a while since I did anything like this (and it was in Oracle, not MySQL) but I favour storing file location, rather than files (the files live on the filesystem) - much more flexible, ime.

Yep, that's the way I would go about it.

You just need to make sure that if you're allowing users to upload files, that the location they are stored in is suitably locked down, so that if they where to say upload a script or malicious software it couldn't execute itself.

 

[Markymark]

Uploading files has 2 issues. One, filtering it so it can only upload specified file types to stop malicicious code. 2nd is ensuring its not available for anyone to find for data protection.

 

[Octet]

I think it all depends on what the ultimate use is, in my oppinion I would do the following:

• If the file is to be downloaded by members of the public, store it as a file and the location in the database
• If you are going to be modifying the data in the file or reading it frequently, parse it and store only the relevant data in the database (like @jefmcg has suggested)
• If you need the file to be parsed by a third party tool, store it as a file on the server
• If members of the public are to be uploading their own file, store it as a file and the location in the database

Remember, if you are storing in the database:

• Parametise your queries to prevent injection attacks
• Store what is relevant, using datatypes that match
• Index your tables to improve efficiency as well as reducing IO operations

If you are storing it as a file:

• Check the file type and size before accepting any file (remember... these can still be spoofed)
• Store the files in a secure area, where they can't be executed or "break out of" the filesystem

And finally:

• NEVER TRUST USER DATA (including data from third party sources)