Take the 2-minute tour ×

I think my question is best explained via an example: Suppose I want to write a webserver for streaming music. I have the following columns in the songs table: |song_id|song_name|song_genre|song_length|artist

I would like to enable my users to save playlists, but I don't want my playlists to be defined by explicitly specifying the songs that are in the playlist, rather by something like "all songs by ringo starr", which would mean that when new songs by ringo starr are added to the table, the playlist will automatically have them. Actually what I want is a table called playlists that contains a list of mysql views.

The most naive approach would be to simply have a table called playlists, and one of it's columns would be called playlist_query which would store for the above example something like the string "select song_id from songs where artist='ringo starr'.

This is of course incredibly insecure, but it would suffice in case there is no better solution, since this application is used only internaly inside our company by just a few employees who are all good programmers that know their way around mysql.

So what do you suggest? go with this ugly solution, or is there some easier way to do this?

share|improve this question
I see nothing wrong with this approach. What's unsecure about it? –  Jan Hančič Oct 7 '10 at 9:51
@Jan - for general usage, allowing users to insert queries to be used by your DB is considered pretty insecure, I believe: en.wikipedia.org/wiki/Sql_injection Assuming that we know that all users are trusted users, someone might still do a mistake, for instance do a join incorrectly (with a join condition that's always met), resulting in trying to select 1000,000,000,000 songs. –  olamundo Oct 7 '10 at 9:53
Just store the "ringo starr" in the table. Build the rest of your query in your code. Do some escaping on the search term and you're set –  Jan Hančič Oct 7 '10 at 15:19
add comment

2 Answers

up vote 1 down vote accepted

You could store the name of the view in the playlists table instead of the query itself.

You'd still have to create the required views though and I don't know how that helps your "security problem".

Could you elaborate on what kind of security is required?

share|improve this answer
add comment

I'd define a Playlist as a list of Filters where a filter might be a song ID, an artist ID, a song name, a genre ... I'd then fetch all songs for each filter, either in a single query combining filers with simple OR or using UNION if you want the playlist to be in the same order as the filters. Note though that it needs some additional effort to get results from UNION in the order of the queries, see this answer.

The advantages of this approach:

  • no millions of views to manage
  • no SQL queries stored in DB
  • pretty flexible filtering

Admittedly, I can't say much about the performance of this solution. Shouldn't be too bad though.

share|improve this answer
add comment

Your Answer


