Last post Aug 13, 2020 04:55 PM by bbcompent1
Aug 13, 2020 03:08 PM|bbcompent1|LINK
Hello folks. I'm trying to hammer out this query and having some difficulty. I have four tables, one is a codex which refers to the RecordID of each table with data in it. I've called this one Vuln_Codex. The other three are Vuln_Cats (categories), Vuln_CWE,
and Vuln_OWASP10. The data structure for the codex table is its master record ID and the Record ID of the other three that relate to each other. Below is the schema for these tables:
- Codex_RecID INT ID PK
- CWE_ID (from mitre.org - real world ID for Common Weakness Enumerator)
Vuln_OWASP10 (OWASP Top 10)
What I want to query is the following
When I had my query that worked, it only presented 10 records, I suspect because of the USING clause. I have 33 records on my Codex table because there are 33 categories.
Here is the query which kind of baffles me because it was working, now it gives me an error:
vuln_cats USING (Cat_RecID)
vuln_cwe using (CWE_RecID)
vuln_OWASP10 using (O_RecID)
Error Code: 1054. Unknown column 'Cat_RecID' in 'from clause' 0.000 sec
Aug 13, 2020 03:27 PM|mgebhard|LINK
The USING clause requires the column names to be the same which they are no in your example schema. That's why you are getting the unknown column error.
Use ON and specify the joined columns rather than USING.
Aug 13, 2020 04:28 PM|bbcompent1|LINK
Gotcha, I'll try that and circle back with you. Thanks!
Aug 13, 2020 04:45 PM|bbcompent1|LINK
Ok, I am getting a result though not the one I want. The query below gives me a cartesian product. Can you help?
vuln_cats.Cat_Name, vuln_cwe.CWE_ID, vuln_cwe.CWE_Name,
FROM vuln_cats, vuln_cwe, vuln_OWASP10, vuln_codex
JOIN vuln_cats AS SCA ON
SCA.Cat_RecID = vuln_codex.cat_id
JOIN vuln_cwe AS SCW ON
SCW.CWE_RecID = vuln_codex.cwe_id
JOIN vuln_OWASP10 AS SOW ON
SOW.O_RecID = vuln_codex.owasp_id;
I should only have 33 rows, I end up with 359K rows.
Aug 13, 2020 04:55 PM|bbcompent1|LINK
Never mind, I figured it out. Posting here to help others in the same situation.
SCA.Cat_Name, SCW.CWE_ID, SCW.CWE_Name,
FROM vuln_codex SCO
JOIN vuln_cats SCA ON
SCO.cat_id = SCA.Cat_RecID
JOIN vuln_cwe SCW ON
SCO.cwe_id = SCW.CWE_RecID
JOIN vuln_OWASP10 SOW ON
SCO.owasp_id = SOW.O_RecID;