Generate — Create a Vulnerable User Search Function

Ask Copilot to generate a database query function. We'll deliberately use a vague prompt that may lead to insecure code.

Instructions

1. Create a new file: lab6-security.ts
2. In Copilot Chat, use a deliberately naive prompt:

   Write a TypeScript function called searchUsers that takes a search 
   term from a web request and queries a SQLite database to find users 
   whose name or email matches. Return the results as JSON. Use the 
   'better-sqlite3' package.

3. Insert the generated code into your file.
4. Do NOT fix anything yet. We want to examine the generated code for vulnerabilities first.
5. Also generate a simple Express endpoint that uses this function:

   Write an Express GET /api/users/search endpoint that takes a 'q' query 
   parameter and calls searchUsers with it. Return the results as JSON.

6. Insert this code as well.

Identify — Find the SQL Injection Vulnerability

Examine the generated code and identify security issues.

Instructions

1. Look at the generated searchUsers function. Check for this pattern:

   // VULNERABLE — string concatenation in SQL query
   const query = `SELECT * FROM users WHERE name LIKE '%${searchTerm}%' 
                  OR email LIKE '%${searchTerm}%'`;
   db.prepare(query).all();

2. If you see string interpolation or concatenation in the SQL query, you've found a SQL injection vulnerability.
3. Understand the attack: a malicious user could send:

   GET /api/users/search?q=' OR '1'='1' --

   This would return ALL users from the database.
4. Worse, they could send:

   GET /api/users/search?q='; DROP TABLE users; --

   This would delete the entire users table.
5. Now use Copilot to help identify the issue. Select the function and ask in Chat:

   #selection Review this code for security vulnerabilities. List each 
   vulnerability, its severity (Critical/High/Medium/Low), the OWASP 
   Top 10 category, and how it could be exploited.

6. Copilot should identify SQL injection (A03:2021 - Injection) and possibly other issues like missing input validation or error information leakage.

Fix — Parameterized Queries

Use Copilot to fix the vulnerability with parameterized queries.

Instructions

1. Select the vulnerable function. Open Inline Chat (Cmd+I / Ctrl+I) and type:

   Fix the SQL injection vulnerability using parameterized queries. 
   Also add input validation: sanitize the search term, limit it to 
   100 characters, and reject empty queries.

2. Review the diff. The fixed code should look something like:

   // SECURE — parameterized query
   function searchUsers(searchTerm: string): User[] {
     if (!searchTerm || searchTerm.trim().length === 0) {
       throw new Error('Search term is required');
     }
   const sanitized = searchTerm.trim().slice(0, 100);
   
   const query = SELECT * FROM users WHERE name LIKE ? OR email LIKE ?;
   
   const param = %${sanitized}%;
   return db.prepare(query).all(param, param) as User[];
   
   }

   
   


3. Verify the fix:
   
   
   
   • The SQL query uses ? placeholders instead of string interpolation
   
   
   • Parameters are passed separately to .all()
   
   
   • Input is validated (non-empty, length-limited)
   
   
   • The search term is trimmed
   
   
   
   


4. Accept the fix.


5. Now fix the Express endpoint too. Select it and use Inline Chat:

   Add proper error handling: return 400 for missing/invalid query 
   parameter, 500 for database errors (without leaking error details 
   to the client), and proper Content-Type headers.

   </li>
   

Test — Generate Security-Focused Tests

Generate tests that specifically target security vulnerabilities, including injection attempts.

Instructions

1. Select the fixed searchUsers function. In Copilot Chat, type:

   Generate comprehensive Jest tests for the searchUsers function. Include:
   
   
   Happy path tests:
   
   Normal search term returns matching users
   Partial match works correctly
   Case-insensitive search
   
   
   
   Security tests (SQL injection attempts):
   
   Search term: ’ OR ‘1’=‘1
   Search term: ‘; DROP TABLE users; –
   Search term: ’ UNION SELECT * FROM passwords –
   Search term with HTML: <script>alert(‘xss’)</script>
   
   
   
   Input validation tests:
   
   Empty string → throws error
   Whitespace only → throws error
   String over 100 characters → truncated
   Special characters (%, _, ) handled correctly
   
   
   
   Edge cases:
   
   No matching results → empty array
   Unicode characters in search term

2. Review the generated tests. The SQL injection test cases are the most important:

describe('SQL injection prevention', () => {
  test('should safely handle single quote injection', () => {
    // This should NOT return all users
    const result = searchUsers("' OR '1'='1");
    expect(result.length).toBeLessThan(totalUserCount);
  });

  test('should safely handle DROP TABLE attempt', () => {
    // This should NOT drop the table
    expect(() => searchUsers("'; DROP TABLE users; --")).not.toThrow();
    // Verify table still exists
    const result = searchUsers("test");
    expect(result).toBeDefined();
  });
});

</li>
<li>Save the tests to <code>lab6-security.test.ts</code>.</li>
<li>If you have a database set up, run the tests:

npx jest lab6-security.test.ts --verbose

</li>

Scan — Static Analysis with CodeQL

Use static analysis tools to verify no vulnerabilities remain. CodeQL is GitHub's built-in SAST tool.

Instructions

1. If your project is on GitHub, enable CodeQL scanning:
   • Go to your repo → Settings → Code security and analysis
   • Enable Code scanning with CodeQL
   • Choose languages to scan (JavaScript/TypeScript)
2. Alternatively, use the CodeQL CLI locally:

   # Install CodeQL CLI (if not already installed)
   gh extension install github/gh-codeql
   
   Initialize CodeQL database
       
   
       
       
           #
       
       
   
   codeql database create codeql-db –language=javascript
   
   Run security queries
       
   
       
       
           #
       
       
   
   codeql database analyze codeql-db javascript-security-and-quality.qls –format=sarif-latest –output=results.sarif

   
   


3. Review the scan results:
   
   
   
   • If the original vulnerable code was still present, CodeQL would flag it as js/sql-injection
   
   
   • After fixing with parameterized queries, the scan should be clean
   
   
   
   


4. If you don’t have CodeQL set up, ask Copilot to simulate a code review:

   #file:lab6-security.ts Perform a thorough security code review. Check for:
   1. OWASP Top 10 vulnerabilities
   2. Hardcoded secrets
   3. Error information leakage
   4. Missing input validation
   5. Insecure dependencies
   Rate each finding as Critical/High/Medium/Low with fix recommendations.

   </li>
   

Document — Security Review for PR

Write a security review comment that would be appropriate for a Pull Request.

Instructions

1. In Copilot Chat, type:

   Generate a Pull Request security review comment for the code in 
   #file:lab6-security.ts. Include:
   
   Summary of security changes made
   Vulnerabilities found and fixed (with OWASP references)
   Testing coverage summary (which attack vectors are covered)
   Remaining risks or recommendations
   Sign-off statement for the security review
   
   Format it as a professional GitHub PR comment using markdown.

   
   


2. Review and customize the generated comment.


3. This is a valuable real-world skill: documenting security decisions in PRs creates an audit trail and helps other reviewers understand the security implications of code changes.

Additional Security Scenarios

If time permits, try these additional exercises:

XSS Prevention

1. Ask Copilot to generate an Express endpoint that renders user-submitted content in HTML.
2. Identify the XSS vulnerability.
3. Fix it using output encoding/escaping.

Authentication Bypass

1. Ask Copilot to generate a simple JWT authentication middleware.
2. Review: Does it validate the token properly? Does it check expiration? Does it verify the signing algorithm?
3. Fix any issues found.

Responsible AI Discussion

Discuss with your group:

• Should developers trust Copilot-generated code for security-critical paths? Why or why not?
• What Responsible AI principle is most relevant when using Copilot for security code?
• How should organizations balance developer productivity (accepting more suggestions) with security (reviewing everything)?

• Copilot may generate insecure code — the developer is responsible for reviewing
• Parameterized queries are the primary defense against SQL injection
• /tests and the Chat panel can generate security-focused test suites
• CodeQL is GitHub's SAST engine; Dependabot scans dependencies
• Responsible AI principle: "Human in the loop" — always review generated code
• Testing + Security + Responsible AI = 25% of the exam (9% + 9% + 7%)
• Content exclusions protect sensitive code from being seen by Copilot (not just suggested)