Harden search against SQL injection #355

New issue

Closed

opened 2023-03-03 16:54:12 +00:00 by p3k · 0 comments

p3k commented

2023-03-03 16:54:12 +00:00

(Migrated from github.com)

SQL database was under “attack” resulting in queries running forever and the corresponding container’s CPU to constantly peak.

The culprit:

SELECT comment.id
FROM content AS COMMENT,
     content AS story,
     site,
     metadata,
     account AS creator,
     account AS modifier
WHERE site.id = 1
  AND comment.prototype = 'Comment'
  AND site.id = comment.site_id
  AND comment.story_id = story.id
  AND story.status in ('public',
                       'shared',
                       'open')
  AND story.comment_mode in ('open')
  AND comment.creator_id = creator.id
  AND comment.modifier_id = modifier.id
  AND creator.status <> 'deleted'
  AND modifier.status <> 'deleted'
  AND comment.prototype = metadata.parent_type
  AND comment.id = metadata.parent_id
  AND metadata.name in ('title',
                        'text')
  //                                    👇
  AND lower(metadata.value) like lower('%')/**/
  AND/**/89/**/LIKE/**/90/**/
  OR/**/1789=1789/**/
  AND/**/('vTSI'/**/LIKE/**/'vTSI%')
GROUP BY comment.id,
         comment.created
ORDER BY comment.created DESC
LIMIT 51

Obviously, I was a bad coder not using prepared statements for the query! 🙀

Anyway, thanks for the heads-up, friendly(?) attacker! 🤗

SQL database was under “attack” resulting in queries running forever and the corresponding container’s CPU to constantly peak. The culprit: ```sql SELECT comment.id FROM content AS COMMENT, content AS story, site, metadata, account AS creator, account AS modifier WHERE site.id = 1 AND comment.prototype = 'Comment' AND site.id = comment.site_id AND comment.story_id = story.id AND story.status in ('public', 'shared', 'open') AND story.comment_mode in ('open') AND comment.creator_id = creator.id AND comment.modifier_id = modifier.id AND creator.status <> 'deleted' AND modifier.status <> 'deleted' AND comment.prototype = metadata.parent_type AND comment.id = metadata.parent_id AND metadata.name in ('title', 'text') // 👇 AND lower(metadata.value) like lower('%')/**/ AND/**/89/**/LIKE/**/90/**/ OR/**/1789=1789/**/ AND/**/('vTSI'/**/LIKE/**/'vTSI%') GROUP BY comment.id, comment.created ORDER BY comment.created DESC LIMIT 51 ``` Obviously, I was a bad coder not using prepared statements for the query! :scream_cat: Anyway, thanks for the heads-up, friendly(?) attacker! :hugs: