{"id":16094,"date":"2025-04-16T10:25:48","date_gmt":"2025-04-16T08:25:48","guid":{"rendered":"https:\/\/www.beseit.net\/?p=16094"},"modified":"2025-04-16T11:08:44","modified_gmt":"2025-04-16T09:08:44","slug":"sql-consulta-preparada-consulta-directa-conn-query","status":"publish","type":"post","link":"https:\/\/www.beseit.net\/?p=16094","title":{"rendered":"SQL consulta preparada &amp; Consulta directa ($conn->query)"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\"><strong>M\u00e8tode 2: Consulta directa (<code>$conn-&gt;query<\/code>)<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>$sql = \"INSERT INTO $taula (catala, castella) VALUES ('$registre_nou', 'registre nou, completar registre')\";\n$result = $conn-&gt;query($sql);\n$respostes = array();<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Pros:<\/strong><\/h3>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Simplicitat <\/strong>:\n<ul class=\"wp-block-list\">\n<li>\u00c9s m\u00e9s curt i f\u00e0cil d&#8217;escriure, especialment per a desenvolupadors novells o per a casos senzills on no hi ha riscos evidents.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>No requereix configuraci\u00f3 addicional:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Funciona directament amb extensions b\u00e0siques com <code>mysqli<\/code> sense necessitat de configurar PDO.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Contres:<\/strong><\/h3>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Vulnerabilitat a injeccions SQL <\/strong>:\n<ul class=\"wp-block-list\">\n<li>Com que les variables (<code>$registre_nou<\/code>) s&#8217;insereixen directament a la cadena SQL, un usuari malintencionat podria introduir codi SQL per manipular la consulta. Per exemple:phpCopiar1$registre_nou = \u00ab&#8216;; DROP TABLE users; &#8211;\u00ab;Aix\u00f2 podria resultar en una consulta perillosa com:sqlCopiar1INSERTINTO taula (catala, castella) VALUES (\u00bb; DROPTABLE users; &#8211;&#8216;, &#8216;registre nou, completar registre&#8217;);<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Manca de maneig de car\u00e0cters especials:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Si <code>$registre_nou<\/code> cont\u00e9 car\u00e0cters especials (com <code>'<\/code> o <code>\\<\/code>), provocar\u00e0 errors sint\u00e0ctics o comportaments inesperats.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Menys flexible:<\/strong>\n<ul class=\"wp-block-list\">\n<li>No permet reutilitzar la consulta amb diferents valors sense reconstruir-la completament.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Gesti\u00f3 d&#8217;errors limitada:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Sense un bloc <code>try-catch<\/code>, \u00e9s m\u00e9s dif\u00edcil detectar i gestionar errors de manera adequada.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Depend\u00e8ncia de l&#8217;extensi\u00f3 <code>mysqli<\/code>\u2063<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Si m\u00e9s tard decideixes canviar a una altra base de dades (com PostgreSQL), haur\u00e0s de reescriure tot el codi relacionat amb <code>mysqli<\/code>.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>M\u00e8tode 1: Consulta preparada (<code>prepared statement<\/code>)<\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>$sql = \"INSERT INTO $taula (catala, castella) VALUES (:catala, :castella)\";\n$stmt = $pdo-&gt;prepare($sql);\n$stmt-&gt;bindParam(':catala', $registre_nou, PDO::PARAM_STR);\n$stmt-&gt;bindValue(':castella', 'registre nou, completar registre', PDO::PARAM_STR);\n$stmt-&gt;execute();\necho \"Registre inserit correctament.\";\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Pros:<\/strong><\/h3>\n\n\n\n<h2 class=\"wp-block-heading\">0 <strong>Seguretat contra injeccions SQL <\/strong><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>$stmt = $pdo-&gt;prepare(\"INSERT INTO users (username, password) VALUES (:username, :password)\");\n$stmt-&gt;bindParam(':username', $username);\n$stmt-&gt;bindParam(':password', $password);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">En aquest cas, encara que <code>$username<\/code> contingui <code>'; DROP TABLE users; --<\/code>, no es podr\u00e0 executar com a part de la consulta SQL.<\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>Maneig de car\u00e0cters especials <\/strong>:\n<ul class=\"wp-block-list\">\n<li>Les consultes preparades escapen autom\u00e0ticament car\u00e0cters especials (com cometes simples <code>'<\/code> o barres invertides <code>\\<\/code>), prevenint errors sint\u00e0ctics.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Flexibilitat <\/strong>\/<strong>Reutilitzaci\u00f3 de consultes<\/strong>\n<ul class=\"wp-block-list\">\n<li>Pots utilitzar el mateix patr\u00f3 de consulta preparada per inserir m\u00faltiples registres canviant nom\u00e9s els valors dels par\u00e0metres.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>$stmt = $pdo-&gt;prepare(\"INSERT INTO users (username, password) VALUES (:username, :password)\");\nforeach ($users as $user) {\n    $stmt-&gt;execute($user);\n}<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">3. <strong>Millor gesti\u00f3 d&#8217;errors:<\/strong><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Amb PDO, pots capturar excepcions (<code>PDOException<\/code>) i gestionar-les de manera estructurada (per exemple, mitjan\u00e7ant blocs <code>try-catch<\/code>).<\/li>\n<\/ul>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>try {\n    $stmt-&gt;execute();\n} catch (PDOException $e) {\n    echo \"Error: \" . $e-&gt;getMessage();\n}<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">4.<strong>Compatibilitat amb bases de dades m\u00faltiples:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>PDO \u00e9s una interf\u00edcie gen\u00e8rica que treballa amb diferents sistemes de gesti\u00f3 de bases de dades (MySQL, PostgreSQL, SQLite, etc.).<\/li>\n<\/ul>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Contres:<\/strong><\/h3>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li><strong>M\u00e9s complexitat inicial <\/strong>:\n<ul class=\"wp-block-list\">\n<li>Requereix m\u00e9s l\u00ednies de codi i una comprensi\u00f3 b\u00e0sica de les consultes preparades i els lligams de par\u00e0metres.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Depend\u00e8ncia de PDO <\/strong>:\n<ul class=\"wp-block-list\">\n<li>Necessites configurar una connexi\u00f3 PDO per utilitzar consultes preparades, cosa que pot ser un pas addicional si est\u00e0s acostumat a usar extensions m\u00e9s antigues com <code>mysqli<\/code>.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>M\u00e8tode 2: Consulta directa ($conn-&gt;query) Pros: Contres: M\u00e8tode 1: Consulta preparada (prepared statement) Pros: 0 Seguretat contra injeccions SQL En aquest cas, encara que $username contingui &#8216;; DROP TABLE users; &#8211;, no es podr\u00e0 executar com a part de la &hellip; <a href=\"https:\/\/www.beseit.net\/?p=16094\">Continua llegint <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":8179,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-16094","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programacio"],"_links":{"self":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts\/16094","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16094"}],"version-history":[{"count":12,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts\/16094\/revisions"}],"predecessor-version":[{"id":16114,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/posts\/16094\/revisions\/16114"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=\/wp\/v2\/media\/8179"}],"wp:attachment":[{"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16094"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16094"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beseit.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16094"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}