355 lines
11 KiB
PHP
355 lines
11 KiB
PHP
<?php
|
|
|
|
namespace Tests\Feature\Security\SQL;
|
|
|
|
use App\Models\User;
|
|
use App\Models\Post;
|
|
use App\Models\Organization;
|
|
use App\Models\Transaction;
|
|
use App\Models\Account;
|
|
use Illuminate\Foundation\Testing\RefreshDatabase;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Tests\TestCase;
|
|
|
|
/**
|
|
* SQL Injection Prevention Tests
|
|
*
|
|
* Tests that the application properly sanitizes user input and uses
|
|
* parameterized queries to prevent SQL injection attacks.
|
|
*
|
|
* @group security
|
|
* @group sql-injection
|
|
* @group critical
|
|
*/
|
|
class SQLInjectionPreventionTest extends TestCase
|
|
{
|
|
use RefreshDatabase;
|
|
|
|
/**
|
|
* Test search functionality prevents SQL injection
|
|
*
|
|
* @test
|
|
*/
|
|
public function search_prevents_sql_injection()
|
|
{
|
|
// Arrange: Create test data
|
|
$user = User::factory()->create();
|
|
Post::factory()->count(3)->create();
|
|
|
|
// SQL injection payloads
|
|
$maliciousQueries = [
|
|
"' OR '1'='1",
|
|
"'; DROP TABLE users--",
|
|
"' UNION SELECT * FROM users--",
|
|
"1' OR '1' = '1')) /*",
|
|
"admin'--",
|
|
"' OR 1=1--",
|
|
"' OR 'x'='x",
|
|
"1; DROP TABLE transactions--",
|
|
];
|
|
|
|
$this->actingAs($user);
|
|
|
|
// Act & Assert: Try each malicious query
|
|
foreach ($maliciousQueries as $query) {
|
|
// Test search endpoint (adjust route as needed)
|
|
$response = $this->get(route('search', ['q' => $query]));
|
|
|
|
// Should return 200 (search results, even if empty)
|
|
// Should NOT execute SQL injection
|
|
$response->assertStatus(200);
|
|
|
|
// Verify tables still exist
|
|
$this->assertTrue(
|
|
DB::getSchemaBuilder()->hasTable('users'),
|
|
"SQL injection attempt deleted users table: {$query}"
|
|
);
|
|
$this->assertTrue(
|
|
DB::getSchemaBuilder()->hasTable('transactions'),
|
|
"SQL injection attempt deleted transactions table: {$query}"
|
|
);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Test profile name input prevents SQL injection
|
|
*
|
|
* @test
|
|
*/
|
|
public function profile_name_input_prevents_sql_injection()
|
|
{
|
|
// Arrange: Create user
|
|
$user = User::factory()->create();
|
|
|
|
// SQL injection payloads
|
|
$maliciousNames = [
|
|
"admin'; DROP TABLE users--",
|
|
"test' OR '1'='1",
|
|
"'; DELETE FROM transactions WHERE '1'='1",
|
|
];
|
|
|
|
$this->actingAs($user);
|
|
|
|
// Act & Assert: Try to update profile with malicious names
|
|
foreach ($maliciousNames as $name) {
|
|
// Note: This should be rejected by validation (alphanumeric rule)
|
|
// But even if validation is bypassed, SQL should be safe
|
|
|
|
$response = $this->put(route('user-profile-information.update'), [
|
|
'name' => $name,
|
|
'email' => $user->email,
|
|
]);
|
|
|
|
// Should either be rejected by validation or safely stored
|
|
// Tables should still exist
|
|
$this->assertTrue(
|
|
DB::getSchemaBuilder()->hasTable('users'),
|
|
"SQL injection via name field succeeded: {$name}"
|
|
);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Test transaction queries use parameterized statements
|
|
*
|
|
* @test
|
|
*/
|
|
public function transaction_queries_use_parameterized_statements()
|
|
{
|
|
// Arrange: Create accounts and transaction
|
|
$user = User::factory()->create();
|
|
$fromAccount = Account::factory()->create([
|
|
'accountable_type' => User::class,
|
|
'accountable_id' => $user->id,
|
|
]);
|
|
$toAccount = Account::factory()->create([
|
|
'accountable_type' => User::class,
|
|
'accountable_id' => $user->id,
|
|
]);
|
|
|
|
Transaction::factory()->create([
|
|
'from_account_id' => $fromAccount->id,
|
|
'to_account_id' => $toAccount->id,
|
|
'amount' => 100,
|
|
]);
|
|
|
|
// SQL injection attempt in description
|
|
$maliciousDescriptions = [
|
|
"Payment'; DROP TABLE transactions--",
|
|
"' OR '1'='1",
|
|
"Test' UNION SELECT * FROM users--",
|
|
];
|
|
|
|
$this->actingAs($user);
|
|
|
|
// Act: Create transactions with malicious descriptions
|
|
foreach ($maliciousDescriptions as $description) {
|
|
try {
|
|
$transaction = Transaction::create([
|
|
'from_account_id' => $fromAccount->id,
|
|
'to_account_id' => $toAccount->id,
|
|
'amount' => 50,
|
|
'description' => $description,
|
|
'transaction_type_id' => 1,
|
|
]);
|
|
|
|
// Description should be stored as-is (string), not executed
|
|
$this->assertEquals($description, $transaction->description);
|
|
|
|
} catch (\Exception $e) {
|
|
// If validation rejects it, that's also acceptable
|
|
$this->assertTrue(true);
|
|
}
|
|
|
|
// Assert: Table still exists
|
|
$this->assertTrue(
|
|
DB::getSchemaBuilder()->hasTable('transactions'),
|
|
"SQL injection via transaction description succeeded"
|
|
);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Test WHERE clause uses parameter binding
|
|
*
|
|
* @test
|
|
*/
|
|
public function where_clauses_use_parameter_binding()
|
|
{
|
|
// Arrange: Create users
|
|
$user1 = User::factory()->create(['name' => 'testuser1']);
|
|
$user2 = User::factory()->create(['name' => 'testuser2']);
|
|
|
|
// SQL injection attempt in filter
|
|
$maliciousFilter = "testuser1' OR '1'='1";
|
|
|
|
// Act: Query with malicious filter
|
|
// This simulates a search or filter operation
|
|
$result = User::where('name', $maliciousFilter)->get();
|
|
|
|
// Assert: Should return empty (no user with that exact name)
|
|
// NOT all users (which would happen if SQL injection succeeded)
|
|
$this->assertCount(0, $result, "SQL injection in WHERE clause succeeded");
|
|
|
|
// Verify both users still exist
|
|
$this->assertCount(2, User::all());
|
|
}
|
|
|
|
/**
|
|
* Test ORDER BY clause prevents injection
|
|
*
|
|
* @test
|
|
*/
|
|
public function order_by_clause_prevents_injection()
|
|
{
|
|
// Arrange: Create posts
|
|
$user = User::factory()->create();
|
|
Post::factory()->count(5)->create();
|
|
|
|
$this->actingAs($user);
|
|
|
|
// Malicious ORDER BY attempts
|
|
$maliciousOrders = [
|
|
"created_at; DROP TABLE posts--",
|
|
"id' OR '1'='1--",
|
|
"(SELECT * FROM users)",
|
|
];
|
|
|
|
// Act & Assert: Try malicious order parameters
|
|
foreach ($maliciousOrders as $order) {
|
|
try {
|
|
// Attempt to order by malicious input
|
|
// In real application, this would be through a query parameter
|
|
$response = $this->get(route('posts.index', ['sort' => $order]));
|
|
|
|
// Should either safely handle or reject
|
|
// Tables should still exist
|
|
$this->assertTrue(
|
|
DB::getSchemaBuilder()->hasTable('posts'),
|
|
"SQL injection via ORDER BY succeeded: {$order}"
|
|
);
|
|
|
|
} catch (\Exception $e) {
|
|
// Exception is acceptable if validation rejects it
|
|
$this->assertTrue(true);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Test raw queries are properly escaped (if any exist)
|
|
*
|
|
* @test
|
|
*/
|
|
public function raw_queries_use_parameter_binding()
|
|
{
|
|
// This test verifies that any raw SQL queries in the codebase
|
|
// use parameter binding instead of string concatenation
|
|
|
|
// Arrange: Create test data
|
|
$user = User::factory()->create();
|
|
|
|
// Example of UNSAFE query (should NOT exist in codebase):
|
|
// DB::select("SELECT * FROM users WHERE name = '" . $input . "'");
|
|
|
|
// Example of SAFE query (should be used):
|
|
// DB::select("SELECT * FROM users WHERE name = ?", [$input]);
|
|
|
|
$maliciousInput = "admin' OR '1'='1--";
|
|
|
|
// Act: Try query with malicious input
|
|
$result = DB::select("SELECT * FROM users WHERE name = ?", [$maliciousInput]);
|
|
|
|
// Assert: Should return empty (no user with that name)
|
|
$this->assertEmpty($result, "Raw query parameter binding failed");
|
|
|
|
// All users should still exist
|
|
$this->assertGreaterThan(0, User::count());
|
|
}
|
|
|
|
/**
|
|
* Test LIKE queries prevent injection
|
|
*
|
|
* @test
|
|
*/
|
|
public function like_queries_prevent_injection()
|
|
{
|
|
// Arrange: Create users
|
|
User::factory()->create(['name' => 'testuser']);
|
|
User::factory()->create(['name' => 'admin']);
|
|
|
|
// Malicious LIKE pattern
|
|
$maliciousPattern = "%' OR '1'='1";
|
|
|
|
// Act: Search with malicious pattern
|
|
$result = User::where('name', 'LIKE', $maliciousPattern)->get();
|
|
|
|
// Assert: Should return empty or safe results
|
|
// Should NOT return all users
|
|
$this->assertLessThan(2, $result->count(), "LIKE injection succeeded");
|
|
}
|
|
|
|
/**
|
|
* Test JSON input prevents injection
|
|
*
|
|
* @test
|
|
*/
|
|
public function json_input_prevents_injection()
|
|
{
|
|
// Arrange: Create user
|
|
$user = User::factory()->create();
|
|
|
|
$this->actingAs($user);
|
|
|
|
// Malicious JSON payload
|
|
$maliciousData = [
|
|
'name' => "test'; DROP TABLE users--",
|
|
'email' => "test@example.com",
|
|
'extra' => "' OR '1'='1",
|
|
];
|
|
|
|
// Act: Submit malicious JSON data
|
|
$response = $this->putJson(route('user-profile-information.update'), $maliciousData);
|
|
|
|
// Assert: Data should be safely handled
|
|
$this->assertTrue(
|
|
DB::getSchemaBuilder()->hasTable('users'),
|
|
"SQL injection via JSON input succeeded"
|
|
);
|
|
}
|
|
|
|
/**
|
|
* Test bulk operations prevent injection
|
|
*
|
|
* @test
|
|
*/
|
|
public function bulk_operations_prevent_injection()
|
|
{
|
|
// Arrange: Create users
|
|
$user1 = User::factory()->create();
|
|
$user2 = User::factory()->create();
|
|
|
|
// Malicious IDs array
|
|
$maliciousIds = [
|
|
$user1->id,
|
|
"1' OR '1'='1--",
|
|
$user2->id,
|
|
];
|
|
|
|
// Act: Try bulk query with malicious IDs
|
|
try {
|
|
$result = User::whereIn('id', $maliciousIds)->get();
|
|
|
|
// Assert: Should only return valid IDs, not all users
|
|
$this->assertLessThanOrEqual(2, $result->count(), "Bulk operation injection succeeded");
|
|
|
|
} catch (\Exception $e) {
|
|
// Exception is acceptable
|
|
$this->assertTrue(true);
|
|
}
|
|
|
|
// All users should still exist
|
|
$this->assertCount(2, User::all());
|
|
}
|
|
}
|