Lesson 5: Database Queries

Learn how to safely query databases and prevent SQL injection attacks.

The Database Class

DALT.PHP uses a simple PDO wrapper:

$db = App::resolve(Database::class);

// Query with parameters
$result = $db->query('SELECT * FROM posts WHERE id = :id', [
    'id' => 123
]);

// Fetch one
$post = $result->find();

// Fetch all
$posts = $result->get();

Prepared Statements

Always use prepared statements to prevent SQL injection:

// ✅ SECURE - Prepared statement
$db->query('SELECT * FROM users WHERE email = :email', [
    'email' => $email
]);

// ❌ VULNERABLE - String concatenation
$db->query("SELECT * FROM users WHERE email = '{$email}'");

Never concatenate user input into SQL queries! This creates SQL injection vulnerabilities.

SQL Injection Explained

The Attack

// Vulnerable code
$email = $_POST['email']; // User input: "' OR '1'='1"
$query = "SELECT * FROM users WHERE email = '{$email}'";
// Result: SELECT * FROM users WHERE email = '' OR '1'='1'
// Returns ALL users!

The Fix

// Secure code
$db->query('SELECT * FROM users WHERE email = :email', [
    'email' => $_POST['email']
]);
// PDO escapes the input, preventing injection

CRUD Operations

Create (INSERT)

$db->query('INSERT INTO posts (title, body, user_id) VALUES (:title, :body, :user_id)', [
    'title' => $_POST['title'],
    'body' => $_POST['body'],
    'user_id' => $_SESSION['user']['id']
]);

Read (SELECT)

// Get one
$post = $db->query('SELECT * FROM posts WHERE id = :id', [
    'id' => $_GET['id']
])->find();

// Get all
$posts = $db->query('SELECT * FROM posts ORDER BY created_at DESC')->get();

// Get with condition
$userPosts = $db->query('SELECT * FROM posts WHERE user_id = :user_id', [
    'user_id' => $userId
])->get();

Update (UPDATE)

$db->query('UPDATE posts SET title = :title, body = :body WHERE id = :id', [
    'title' => $_POST['title'],
    'body' => $_POST['body'],
    'id' => $_GET['id']
]);

Delete (DELETE)

$db->query('DELETE FROM posts WHERE id = :id', [
    'id' => $_GET['id']
]);

Query Methods

find()

Returns single row or null:

$user = $db->query('SELECT * FROM users WHERE id = :id', ['id' => 1])->find();

if ($user) {
    echo $user['email'];
} else {
    echo 'User not found';
}

findOrFail()

Returns single row or aborts with 404:

$post = $db->query('SELECT * FROM posts WHERE id = :id', [
    'id' => $_GET['id']
])->findOrFail();

// If not found, automatically shows 404 page

get()

Returns array of all rows:

$posts = $db->query('SELECT * FROM posts')->get();

foreach ($posts as $post) {
    echo $post['title'];
}

Common Patterns

Pagination

$perPage = 10;
$page = $_GET['page'] ?? 1;
$offset = ($page - 1) * $perPage;

$posts = $db->query(
    'SELECT * FROM posts LIMIT :limit OFFSET :offset',
    ['limit' => $perPage, 'offset' => $offset]
)->get();

Search

$search = '%' . $_GET['q'] . '%';

$results = $db->query(
    'SELECT * FROM posts WHERE title LIKE :search OR body LIKE :search',
    ['search' => $search]
)->get();

Joins

$posts = $db->query('
    SELECT posts.*, users.email as author_email
    FROM posts
    JOIN users ON posts.user_id = users.id
    WHERE posts.published = 1
')->get();

Counting

$count = $db->query('SELECT COUNT(*) as count FROM posts')->find();
echo $count['count'];

Database Configuration

SQLite (Default)

DB_CONNECTION=sqlite
DB_DATABASE=database/database.sqlite

PostgreSQL

DB_CONNECTION=pgsql
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=daltphp
DB_USERNAME=postgres
DB_PASSWORD=secret

DB_HOST=localhost DB_PORT=5432 DB_DATABASE=daltphp DB_USERNAME=postgres DB_PASSWORD=secret


## Migrations

Create database tables with migrations:

```php
// database/migrations/001_create_posts_table.php
return [
    'up' => "
        CREATE TABLE posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title VARCHAR(255) NOT NULL,
            body TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    ",
    'down' => "DROP TABLE posts"
];

Run migrations:

php artisan migrate

Debugging Queries

Dump Query

$query = 'SELECT * FROM posts WHERE id = :id';
$params = ['id' => 123];

dd([
    'query' => $query,
    'params' => $params
]);

Check Connection

$db = App::resolve(Database::class);
dd($db->connection);

View Results

$posts = $db->query('SELECT * FROM posts')->get();
dd($posts);

Security Best Practices

1. Always Use Prepared Statements

// ✅ SECURE
$db->query('SELECT * FROM users WHERE email = :email', ['email' => $email]);

// ❌ VULNERABLE
$db->query("SELECT * FROM users WHERE email = '{$email}'");

2. Validate Input

if (!Validator::email($_POST['email'])) {
    $errors['email'] = 'Invalid email';
}

3. Escape Output

// In views
<h1><?= htmlspecialchars($post['title']) ?></h1>

4. Use Transactions

try {
    $db->connection->beginTransaction();
    
    $db->query('INSERT INTO posts ...', $data);
    $db->query('UPDATE users ...', $userData);
    
    $db->connection->commit();
} catch (Exception $e) {
    $db->connection->rollBack();
    throw $e;
}

Remember: The database is only as secure as your queries. Always use prepared statements!

Database Queries