Markdown Reader

SQL_GUIDE

SQL Class - Guide d'utilisation #

🎯 Philosophie #

La classe `SQL` combine le meilleur de:

Creole - SQL brut avec prepared statements
Java JDBC - Style PreparedStatement
le Sanitizer - Validation automatique des données

📦 Installation #

cp SQL.php lib/database/

Dans le code:

use Beamreactor\Database\SQL;

// C'est tout! Le lazy loading fait le reste

💡 Utilisation de base #

SELECT - Plusieurs lignes #

use Beamreactor\Database\SQL;

// MAINTENANT (SQL)
$users = SQL::query(
	"SELECT * FROM users WHERE level >= ?", 
	[100],		  // Paramètres
	['int']		 // Types pour sanitizer
);

foreach ($users as $user) {
	echo $user['username'];
}

SELECT - Une seule ligne #


$user = SQL::queryFirst(
	"SELECT * FROM users WHERE id = ?",
	[$_GET['id']],   // Pas besoin de (int), sanitizer le fait!
	['int']
);

SELECT - Une seule valeur #

// Compter les users
$count = SQL::queryValue(
	"SELECT COUNT(*) FROM users WHERE status = ?",
	['active'],
	['string']
);

// Récupérer un email
$email = SQL::queryValue(
	"SELECT email FROM users WHERE id = ?",
	[123],
	['int']
);

INSERT #

// Méthode 1: SQL brut
$user_id = SQL::insert(
	"INSERT INTO users (username, email, level) VALUES (?, ?, ?)",
	['alice', 'alice@example.com', 100],
	['string', 'email', 'int']
);

// Méthode 2: Helper (auto-détecte les types)
$user_id = SQL::insertRow('users', [
	'username' => 'alice',
	'email' => 'alice@example.com',
	'level' => 100,
	'created_at' => date('Y-m-d H:i:s')
]);

UPDATE #

// Méthode 1: SQL brut
$affected = SQL::execute(
	"UPDATE users SET status = ?, last_login = ? WHERE id = ?",
	['active', date('Y-m-d H:i:s'), 123],
	['string', 'string', 'int']
);

// Méthode 2: Helper
$affected = SQL::updateRow('users',
	['status' => 'active', 'last_login' => date('Y-m-d H:i:s')],  // Data
	['id' => 123]  // Where
);

DELETE #

// Méthode 1: SQL brut
$deleted = SQL::execute(
	"DELETE FROM sessions WHERE expires < ?",
	[time()],
	['int']
);

// Méthode 2: Helper
$deleted = SQL::deleteRow('sessions', ['user_id' => 123]);

🔥 Exemples pratiques #

use Beamreactor\Database\SQL;
use Beamreactor\Core\Sessions;

// Récupérer user depuis formulaire (sanitization automatique!)
$user = SQL::queryFirst(
	"SELECT * FROM users WHERE username = ?",
	[$_POST['username']],
	['string']
);

if ($user && password_verify($_POST['password'], $user['password'])) {
	// Update last login
	SQL::execute(
		"UPDATE users SET last_login = ? WHERE id = ?",
		[date('Y-m-d H:i:s'), $user['id']],
		['string', 'int']
	);
	
	Sessions::set('user_id', $user['id']);
	echo "Login successful!";
} else {
	echo "Invalid credentials";
}

Exemple 2: Recherche avec LIKE #

$search = $_GET['search']; // Pas besoin de cleandata(), SQL le fait!

$results = SQL::query(
	"SELECT * FROM posts 
	 WHERE title LIKE ? OR content LIKE ?
	 ORDER BY created_at DESC
	 LIMIT 50",
	["%$search%", "%$search%"],
	['string', 'string']
);

foreach ($results as $post) {
	echo "<h2>{$post['title']}</h2>";
}

Exemple 3: Pagination #

$page = $_GET['page'] ?? 1;
$per_page = 20;
$offset = ($page - 1) * $per_page;

// Count total
$total = SQL::queryValue(
	"SELECT COUNT(*) FROM posts WHERE status = ?",
	['published'],
	['string']
);

// Get page
$posts = SQL::query(
	"SELECT * FROM posts 
	 WHERE status = ?
	 ORDER BY created_at DESC 
	 LIMIT ? OFFSET ?",
	['published', $per_page, $offset],
	['string', 'int', 'int']
);

$total_pages = ceil($total / $per_page);

Exemple 4: JOIN complexe #

$posts_with_authors = SQL::query(
	"SELECT 
		p.*,
		u.username,
		u.email,
		COUNT(c.id) as comment_count
	 FROM posts p
	 LEFT JOIN users u ON p.user_id = u.id
	 LEFT JOIN comments c ON p.id = c.post_id
	 WHERE p.status = ? AND p.created_at > ?
	 GROUP BY p.id
	 ORDER BY p.created_at DESC
	 LIMIT ?",
	['published', '2024-01-01', 10],
	['string', 'string', 'int']
);

foreach ($posts_with_authors as $post) {
	echo "{$post['title']} by {$post['username']} ({$post['comment_count']} comments)\n";
}

Exemple 5: Transaction #

try {
	SQL::beginTransaction();
	
	// Insert user
	$user_id = SQL::insertRow('users', [
		'username' => 'bob',
		'email' => 'bob@example.com',
		'level' => 100
	]);
	
	// Insert profile
	SQL::insertRow('user_profiles', [
		'user_id' => $user_id,
		'bio' => 'Hello world',
		'website' => 'https://example.com'
	]);
	
	// Insert settings
	SQL::insertRow('user_settings', [
		'user_id' => $user_id,
		'notifications' => 1
	]);
	
	SQL::commit();
	echo "User created!";
	
} catch (Exception $e) {
	SQL::rollback();
	error_log("Error: " . $e->getMessage());
	echo "Error creating user";
}

Exemple 6: Vérifier l'existence #

// Check si email existe
$exists = SQL::exists(
	"SELECT 1 FROM users WHERE email = ?",
	[$_POST['email']],
	['email']
);

if ($exists) {
	echo "Email already registered";
} else {
	// Créer le compte
}

Exemple 7: Mise à jour conditionnelle #

// Update seulement si user est propriétaire
$affected = SQL::execute(
	"UPDATE posts 
	 SET title = ?, content = ?, updated_at = ?
	 WHERE id = ? AND user_id = ?",
	[$title, $content, date('Y-m-d H:i:s'), $post_id, $_SESSION['user_id']],
	['string', 'string', 'string', 'int', 'int']
);

if ($affected > 0) {
	echo "Post updated";
} else {
	echo "Post not found or unauthorized";
}

📋 Types de sanitization supportés #

Tu peux utiliser tous les types de le Sanitizer:

'string'	 // String basique
'int'		// Integer
'float'	  // Float/Double
'bool'	   // Boolean
'email'	  // Email address
'url'		// URL
'date'	   // Date
'uuid'	   // UUID/Key
'ip'		 // IP address
'name'	   // Name (alphabetic)
'filepath'   // File path

Exemples:

// Email validation
$user = SQL::queryFirst(
	"SELECT * FROM users WHERE email = ?",
	[$_POST['email']],
	['email']  // Sanitizer valide que c'est un email!
);

// URL validation
SQL::updateRow('user_profiles',
	['website' => $_POST['website']],
	['user_id' => $user_id]
); // Type détecté automatiquement

🔄 Migration depuis ancien code #

Migration depuis mysqli #

// AVANT
$username = cleandata($_POST['username']);
$level = (int)$_POST['level'];
$result = mysqli_query($db_ressource, 
	"SELECT * FROM users WHERE username = '$username' AND level >= $level"
);
while ($row = mysqli_fetch_assoc($result)) {
	// ...
}
mysqli_free_result($result);

// APRÈS
$users = SQL::query(
	"SELECT * FROM users WHERE username = ? AND level >= ?",
	[$_POST['username'], $_POST['level']],
	['string', 'int']
);

foreach ($users as $user) {
	// ...
}

Migration depuis Creole #

// AVANT
$rs = $db->executeQuery(
	"SELECT * FROM posts WHERE user_id = ? AND status = ?",
	[$user_id, 'published']
);
while ($post = $rs->next()) {
	// ...
}

// APRÈS
$posts = SQL::query(
	"SELECT * FROM posts WHERE user_id = ? AND status = ?",
	[$user_id, 'published'],
	['int', 'string']
);

foreach ($posts as $post) {
	// ...
}

Migration depuis le PDO bricolé #

// AVANT
$user = $pdodb->pquery("SELECT * FROM users WHERE id = ?", $user_id);

// APRÈS
$user = SQL::queryFirst("SELECT * FROM users WHERE id = ?", [$user_id], ['int']);

⚡ Performance #

Query caching #

Les prepared statements sont automatiquement mis en cache:

// Première fois: prepare + execute
$user1 = SQL::queryFirst("SELECT * FROM users WHERE id = ?", [1], ['int']);

// Deuxième fois: utilise le cache, juste execute
$user2 = SQL::queryFirst("SELECT * FROM users WHERE id = ?", [2], ['int']);

Batch operations #

Pour des insertions multiples:

SQL::beginTransaction();

foreach ($users_data as $user) {
	SQL::insertRow('users', $user);
}

SQL::commit(); // Commit une seule fois

🔒 Sécurité #

Sanitization automatique #

// ✅ SÉCURISÉ - Sanitization automatique
$user = SQL::queryFirst(
	"SELECT * FROM users WHERE username = ?",
	[$_POST['username']],  // Automatiquement sanitizé!
	['string']
);

// ❌ DANGEREUX - Concaténation directe (ne JAMAIS faire ça!)
$sql = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

Désactiver la sanitization (si besoin) #

// Si tu as déjà sanitizé manuellement
SQL::setAutoSanitize(false);

$user = SQL::queryFirst("SELECT * FROM users WHERE id = ?", [$id]);

// Réactiver
SQL::setAutoSanitize(true);

Types stricts #

// Le sanitizer force le type
$id = SQL::queryValue(
	"SELECT id FROM users WHERE username = ?",
	['alice'],
	['string']
); // Retourne un INT, même si 'alice' est string

// ✅ Protection contre type juggling

🐛 Debugging #

Voir les requêtes #

// Activer les erreurs PDO (déjà fait dans Connection.php)
// Les exceptions seront lancées automatiquement

try {
	$users = SQL::query("SELECT * FROM users WHERE level >= ?", [100], ['int']);
} catch (PDOException $e) {
	error_log("SQL Error: " . $e->getMessage());
	echo "Database error occurred";
}

Logger les requêtes #

// Wrapper custom pour logger
function loggedQuery($sql, $params, $types) {
	error_log("SQL: $sql");
	error_log("Params: " . json_encode($params));
	error_log("Types: " . json_encode($types));
	
	return SQL::query($sql, $params, $types);
}

$users = loggedQuery(
	"SELECT * FROM users WHERE level >= ?",
	[100],
	['int']
);

📊 Comparaison #

|--------|--------|--------|-----------|

| Type safety | ❌ Non | ⚠️ Limité | ✅ Oui |

🎓 Best practices #

1. Toujours spécifier les types #

// ✅ BON
$user = SQL::queryFirst("SELECT * FROM users WHERE id = ?", [123], ['int']);

// ⚠️ ACCEPTABLE (défaut: string)
$user = SQL::queryFirst("SELECT * FROM users WHERE id = ?", [123]);

// ❌ ÉVITER (pas de param = pas de protection)
$user = SQL::queryFirst("SELECT * FROM users WHERE id = 123");

2. Utiliser les helpers pour CRUD simple #

// ✅ Simple et lisible
$id = SQL::insertRow('users', ['username' => 'alice', 'email' => 'alice@test.com']);

// ⚠️ Plus verbeux mais plus flexible
$id = SQL::insert(
	"INSERT INTO users (username, email) VALUES (?, ?)",
	['alice', 'alice@test.com'],
	['string', 'email']
);

3. Transactions pour opérations multiples #

// ✅ Atomique
SQL::beginTransaction();
try {
	SQL::insertRow('orders', [...]);
	SQL::updateRow('products', ['stock' => 'stock - 1'], ['id' => $product_id]);
	SQL::commit();
} catch (Exception $e) {
	SQL::rollback();
}

📝 Checklist migration #

[ ] Copier `SQL.php` dans `lib/database/`
[ ] Identifier tous les `mysqli_query()`
[ ] Identifier tous les `$db->executeQuery()` (Creole)
[ ] Remplacer par `SQL::query()` ou `SQL::queryFirst()`
[ ] Ajouter les types de sanitization
[ ] Tester chaque fonction migrée
[ ] Supprimer l'ancien code mysqli/Creole
[ ] Profiter! 🎉

---

Version: 1.0.0

Date: 21 novembre 2025

Auteur: Treveur Bretaudière