Here's a screenshot of one, for future reference.

Kernel_6.3_Test_Week_sql_injection_attacks.png

Is anyone knowledgeable enough to figure out whether those attacks have been successful? Whether they actually managed to modify/retrieve something from the database outside of what we normally allow? Or whether they managed to inject some nasty content into the content that we display (e.g. in the Comments section), perhaps together with our hyperlink detection or similar? E.g. create links which would steal your FAS auth cookie or something.
I think that's the first major thing to resolve, whether we have some security hole in there.

The second major thing to resolve is all that junk that gets created and displayed after the attackers tried to break in. It's basically the same problem as spam. I see two missing pieces in there:
1. Allow the admin to clean junk submissions. Currently the admin interface doesn't offer that, and we don't even have authentication for admin actions (see #35).
2. Prevent/limit the junk (attack/spam) submissions from occurring in the future. Unfortunately I don't see how to prevent it without authentication (FAS). A great benefit of test days is that anyone can participate and submit results, even without a Fedora account. But then also anyone can spam us. I guess we were just lucky so far that we didn't need to deal with this. If we enforce FAS auth, the attackers would need to create a Fedora account, a we can rate limit their submissions afterwards, and also allow the admin to mass-remove all content created by a particular user. Is there a better way?

I'm afraid all of this is quite a big task to do :disappointed:

What I'd propose to do with this mess:

• Shut down testdays app
• Restore db from backup[0]
• Restore the testdays web app in read-only until we implement FAS auth for user and admin actions (I'd guess that could be done in week-ish time-frame)

[0] It looks like the other results weren't successfully tampered with, with some degree of confidence. But the cleaning up of the spam doesn't seem like it'll be easily doable in automated way (feel free to correct me). I believe infra does backup the databases every day.

Is there a better way?

reCAPTCHA could help, rate limiting/throttling results submissions could help too, but none of that would make the protection robust enough, imo.

@sumantrom Can you please export all testday results of this cycle from all unaffected testdays to the wiki right now?

But the cleaning up of the spam doesn't seem like it'll be easily doable in automated way (feel free to correct me).

It seems that all of them contain "#123456" in the Comment column, so perhaps this could be easy enough to filter and remove those rows. But I haven't looked at the db schema yet.

What I'd propose to do with this mess:

• Shut down testdays app
• Restore db from backup[0]
• Restore the testdays web app in read-only until we implement FAS auth for user and admin actions (I'd guess that could be done in week-ish time-frame)

[0] It looks like the other results weren't successfully tampered with, with some degree of confidence. But the cleaning up of the spam doesn't seem like it'll be easily doable in automated way (feel free to correct me). I believe infra does backup the databases every day.

We have 2 and possible more test days coming up in this week... how long will the shutdown last?

@sumantrom Can you please export all testday results of this cycle from all unaffected testdays to the wiki right now?

But the cleaning up of the spam doesn't seem like it'll be easily doable in automated way (feel free to correct me).

It seems that all of them contain "#123456" in the Comment column, so perhaps this could be easy enough to filter and remove those rows. But I haven't looked at the db schema yet.

I can export the results today (possibly in 1 hr)

@frantisekz If you can, please have a look at when the attacks actually happened. We'll need to know it if we decide to restore the db anyway (but hopefully it's not needed). And it would be useful to know whether the attacks happened yesterday or a week back (and no attack since then), because then we could make a call whether to shut it down asap or not.

Frantisek says the attack seems to have happened between July 16-17th, i.e. a month back. If that's really the case, I don't feel we need to shut it down immediately/long-term, unless we find out that we really have some security problem in there. Spam in comments is very annoying, but as long as we're not bombarded right now, it seems better to keep it running for test days purposes and work on spam prevention in the mean time.

I'll relay another Frantisek's comment here, to have all conversation in a single place:

From a quick look into db, the attacks didn't achieve anything apart from spamming the testday pages. The libraries used in ceployment that the app is built on top of are kept up2date by me, and it looks like they'd worked correctly in sanitizing user supplied input. On the other hand, the possible impact/scope of a hypothetical successful attack is minimal due to the primitive nature of the testdays app.

Thanks a lot for looking into it.

Worst case outcome would be redirects/spams in our app, we don't store any user data.

We don't store sensitive data, but we display user-provided content. So it's possible to trick us into displaying malicious hyperlinks which then execute some javascript in the user session, to steal some session data. I think it's called cross-site scripting? I might be wrong. But it seems the current attack was different, trying to control the database instead (talking as a zero security expert).

I'll relay another Frantisek's comment here, to have all conversation in a single place:

From a quick look into db, the attacks didn't achieve anything apart from spamming the testday pages. The libraries used in ceployment that the app is built on top of are kept up2date by me, and it looks like they'd worked correctly in sanitizing user supplied input. On the other hand, the possible impact/scope of a hypothetical successful attack is minimal due to the primitive nature of the testdays app.

Thanks a lot for looking into it.

Worst case outcome would be redirects/spams in our app, we don't store any user data.

We don't store sensitive data, but we display user-provided content. So it's possible to trick us into displaying malicious hyperlinks which then execute some javascript in the user session, to steal some session data. I think it's called cross-site scripting? I might be wrong. But it seems the current attack was different, trying to control the database instead (talking as a zero security expert).

I agree we don't store anything personal or super sensitive. XSS unless stored and exploited, it's a very user/client side thing. I actually saw the Kernel 6.4 records which had The payloads

3456'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE('g',2)='g
23. #123456, 123456'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE('g',0)='g
24. #123456, 123456/**/and/**/2=DBMS_PIPE.RECEIVE_MESSAGE('p',2)
25. #123456, 123456/**/and/**/2=DBMS_PIPE.RECEIVE_MESSAGE('l',0)
26. #123456, 123456'and(select+1)>0waitfor/**/delay'0:0:2
27. #123456, 123456'and(select+1)>0waitfor/**/delay'0:0:0
28. #123456, 123456/**/and(select+1)>0waitfor/**/delay'0:0:2'/**/
29. #123456, 123456
30. #123456, 123456/**/and(select+1)>0waitfor/**/delay'0:0:0'/**/
31. #123456, 123456
32. #123456, 123456'/**/and(select'1'from/**/pg_sleep(2))::text>'0
33. #123456, 123456'/**/and(select'1'from/**/pg_sleep(0))::text>'0
34. #123456, 123456/**/and(select+1/**/from/**/pg_sleep(2))>0/**/
35. #123456, 123456/**/and(select+1/**/from/**/pg_sleep(0))>0/**/
36. #123456, 123456"and(select*from(select+sleep(2))a/**/union/**/select+1)="
37. #123456, 123456"and(select*from(select+sleep(0))a/**/union/**/select+1)="
38. #123456, 123456'and(select*from(select+sleep(2))a/**/union/**/select+1)='
39. #123456, 123456'and(select*from(select+sleep(0))a/**/union/**/select+1)='
40. #123456, (select*from(select+sleep(2)union/**/select+1)a)
41. #123456, 123456
42. #123456, (select*from(select+sleep(0)union/**/select+1)a)
43. #123456, 123456 

These are examples of SQL injection attack payloads, specifically time-based blind SQL injection. In time-based blind SQL injection attacks, an attacker tries to determine if a hypothesis is true based on how long it takes for a page to respond. If the SQL statement they inject into the application causes a delay, they infer that the hypothesis might be true.

Here's a breakdown of the payloads:

DBMS_PIPE.RECEIVE_MESSAGE: These payloads are specific to Oracle databases. They cause the database to wait for a specified duration ('g',2 would make it wait for 2 seconds) and if the page takes longer to load, the attacker knows that their injection worked.

waitfor delay: This is specific to Microsoft's SQL Server. It makes the server wait for a specified amount of time.

pg_sleep: This function is specific to PostgreSQL databases. It pauses execution for a specified number of seconds.

sleep: This is a common function that can be used in many SQL databases to introduce a delay.

These payloads aim to determine if the application is vulnerable by introducing delays. If, for instance, an attacker injects a delay of 2 seconds (sleep(2)) and the page takes about 2 seconds longer to load, it's likely that the application is vulnerable.

On the other hand, if the application doesn't show any delay, it doesn't necessarily mean it's not vulnerable. It just means that the specific payload didn't work, and attackers might try other techniques or payloads. That's how we got the huge spam junk. There is absolutely nothing to do about Man In the Middle (MIM) or redirection which can be done using TCP intercept with Burpsuite. At this point rate limiting sounds like a good idea. Captcha after a particular amount of submissions in under 20-30seconds is also a good idea.

I think we might also be alert magazine team (by extension council) - however they have spamassasin WP plugin to take care of these but it will be a good knowledge to have. If it happened here, it might be happening elsewhere as well.

Frantisek managed to remove (hopefully all) the junk results from the database. Kudos to Frantisek, send him a reward.

Frantisek managed to remove (hopefully all) the junk results from the database. Kudos to Frantisek, send him a reward.

Thank you Frantisek, @frantisekz's rewardzone points are @sdharane's clicks away :)

Frantisek managed to remove (hopefully all) the junk results from the database. Kudos to Frantisek, send him a reward.

Thank you Frantisek, @frantisekz's rewardzone points are @sdharane's clicks away :)

Thanks guys, really appreciate it, but on the other hand, I am just doing my job :)

• Restore the testdays web app in read-only until we implement FAS auth for user and admin actions (I'd guess that could be done in week-ish time-frame)

So, Frantisek, are you willing to take this ticket? I most certainly don't feel like implementing FAS auth.

There's nothing too sensitive in here, so I'll convert this ticket to public, so that it can get migrated to Fedora Forge.

This issue has been migrated to Fedora Forge:
https://forge.fedoraproject.org/quality/testdays-web/issues/36

Please continue any further discussion there.