Apolena supports an application written by contractors many years ago. It tracks user activity for reporting purposes, as one does. They then want to report on this, because why else are you gathering this information?
The contractor supplied this query to do the work.
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( su.social_network,'fb','Facebook'), 'custom','Custom Login'),'free','Free WiFi'), 'twitter','Twitter'),'linkedin','LinkedIn'), 'vkontakte','VKontakte'),'instagram','Instagram'), 'Twilio','SMS'),'messenger','Messenger'), 'password','Passcode') AS 'Login Type', st.return+1 as Visits, st.user_table_id, st.social_network, st.picture_url, st.email AS email, CONCAT(st.gender,if(st.age!='',concat(', ',st.age),'')) AS Gender, device, sum(sts.sessiontime) AS 'Session(min)', CONCAT(sum(sts.download),' | ', sum(sts.upload)) AS 'Down | Up (MB)' FROM user_table AS st JOIN user_table_sessions AS sts ON st.user_table_id = sts.stid LEFT JOIN location AS l ON sr.location_id=l.id WHERE 1=1
REPLACE line has had whitespace added, as it was one giant line.
Which, that particular line is… one way to handle turning internal strings into display strings. It's the wrong way, but it's a way.
It's worth noting that the column names on the query are the names that are actually displayed on the report. Which makes the
Gender column interesting, as if the user has a registered age, that becomes part of their gender. Imagine the doctor with the new parents: "Congratulations! It's a (boy,0)!"
As a final naming issue,
sum(sts.sessiontime) which is stored in seconds.
Regardless, the fix for this query is to REPLACE(REPLACE(REPLACE(REPLACE()))) it.
This post originally appeared on The Daily WTF.