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
The 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, Session(min)
displays 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.