New queries for regression

[erizocosmico]

Leak detection on all refs:

SELECT f.repository_id, f.blob_hash, f.commit_hash, f.file_path
FROM (
    SELECT repository_id, commit_hash, blob_hash, file_path
    FROM commit_files
    WHERE file_path NOT REGEXP '^vendor.*'
) f
INNER JOIN (
    SELECT blob_hash, repository_id
    FROM blobs
    WHERE NOT IS_BINARY(blob_content) AND (
        blob_content REGEXP '(?i)facebook.*[\'\\"][0-9a-f]{32}[\'\\"]'
        OR blob_content REGEXP '(?i)twitter.*[\'\\"][0-9a-zA-Z]{35,44}[\'\\"]'
        OR blob_content REGEXP '(?i)github.*[\'\\"][0-9a-zA-Z]{35,40}[\'\\"]'
        OR blob_content REGEXP 'AKIA[0-9A-Z]{16}'
        OR blob_content REGEXP '(?i)heroku.*[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}'
        OR blob_content REGEXP '.*-----BEGIN ((RSA|DSA|OPENSSH|SSH2|EC) )?PRIVATE KEY-----.*'
    )
) h
ON h.blob_hash = f.blob_hash
    AND h.repository_id = f.repository_id

Leaks detection on HEAD

SELECT f.repository_id, f.blob_hash, f.commit_hash, f.file_path
FROM (
    SELECT cf.repository_id, cf.commit_hash, cf.blob_hash, cf.file_path
    FROM refs r
    INNER JOIN commit_files cf
        ON r.repository_id = cf.repository_id
            AND r.commit_hash = cf.commit_hash
            AND r.ref_name = 'HEAD'
            AND cf.file_path NOT REGEXP '^vendor.*'
) f
INNER JOIN (
    SELECT cb.blob_hash, cb.commit_hash, cb.repository_id
    FROM refs r
    INNER JOIN commit_blobs cb
        ON r.repository_id = cb.repository_id AND r.commit_hash = cb.commit_hash
    INNER JOIN blobs b
        ON cb.repository_id = b.repository_id AND cb.blob_hash = b.blob_hash
    WHERE r.ref_name = 'HEAD' AND NOT IS_BINARY(b.blob_content) AND (
        b.blob_content REGEXP '(?i)facebook.*[\'\\"][0-9a-f]{32}[\'\\"]'
        OR b.blob_content REGEXP '(?i)twitter.*[\'\\"][0-9a-zA-Z]{35,44}[\'\\"]'
        OR b.blob_content REGEXP '(?i)github.*[\'\\"][0-9a-zA-Z]{35,40}[\'\\"]'
        OR b.blob_content REGEXP 'AKIA[0-9A-Z]{16}'
        OR b.blob_content REGEXP '(?i)heroku.*[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}'
        OR b.blob_content REGEXP '.*-----BEGIN ((RSA|DSA|OPENSSH|SSH2|EC) )?PRIVATE KEY-----.*'
    )
) h
ON h.blob_hash = f.blob_hash
    AND h.commit_hash = f.commit_hash
    AND h.repository_id = f.repository_id

Has README

SELECT DISTINCT repository_id
FROM refs r
NATURAL JOIN tree_entries te
WHERE r.ref_name = 'HEAD'
    AND te.tree_entry_name REGEXP '^(?i)readme(\\.[a-z]+)?$'

Has license

SELECT DISTINCT repository_id
FROM refs r
NATURAL JOIN tree_entries te
WHERE r.ref_name = 'HEAD'
    AND te.tree_entry_name REGEXP '^(?i)license(\\.[a-z]+)?$'

[ajnavarro]

Closed by src-d/gitbase#694