Parameterized SQL statement: Difference between revisions

Using a SQL update statement like this one (spacing is optional):

SET name = 'Smith, Steve', score = 42, active = true

<blockquote cite="http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/">Non-parameterized SQL is the GoTo statement of database programming. Don't do it, and make sure your coworkers don't either.</blockquote>

=={{header|8th}}==

db @

"UPDATE players SET name=?1,score=?2,active=?3 WHERE jerseyNum=?4"

\ execute the query

=={{header|Ada}}==

with GNATCOLL.SQL_Impl; use GNATCOLL.SQL_Impl;

with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;

Free (Conn);

Free (DB_Descr);

=={{header|Arturo}}==

createTable: function [][

print ["getting user with name: JohnDoe =>" findUser "JohnDoe"]

{{out}}

Tested with gcc version 4.9.2 (Raspbian 4.9.2-10) and SQLite 3.8.7.1

#include <stdlib.h>

#include <sqlite3.h>

}

printf("\n");

{{out}}

=={{header|C sharp|C#}}==

using System.Data.SqlClient;

}

}

=={{header|C++}}==

{{libheader|Qt}}

This example uses the Qt SQL module to access an ODBC data source.

#include <iostream>

}

return 0;

=={{header|Clojure}}==

; Using h2database for this simple example.

(def db {:classname "org.h2.Driver"

; As an alternative to update!, use execute!

=={{header|F_Sharp|F#}}==

{{trans|C#}}

[<EntryPoint>]

tCommand.ExecuteNonQuery() |> ignore

=={{header|Go}}==

import (

}

rows.Close()

{{out}}

<pre>

Example uses the [http://hackage.haskell.org/package/HDBC <tt>HDBC</tt>] package:

import Database.HDBC (IConnection, commit, run, toSql)

main :: IO ()

You'll need an instance of a type with an instance for the <tt>IConnection</tt> type class in order to use this function, such as [http://hackage.haskell.org/package/HDBC-postgresql-2.3.2.5/docs/Database-HDBC-PostgreSQL.html#t:Connection <tt>Connection</tt>] from [http://hackage.haskell.org/package/HDBC-postgresql <tt>HDBC-postgresql</tt>].

=={{header|Huginn}}==

import Algorithms as algo;

import FileSystem as fs;

}

return ( 0 );

=={{header|Java}}==

import java.sql.DriverManager;

import java.sql.Connection;

}

}

=={{header|Julia}}==

Uses the SQLite package.

name = "Smith, Steve"

tbl = SQLite.query(db, "SELECT * from players")

=={{header|Kotlin}}==

import java.sql.DriverManager

}

conn.close()

=={{header|M2000 Interpreter}}==

Module Parametrized_Sql {

Base "rosetta" ' warning erase database if found it in current directory

}

Parametrized_Sql

=={{header|Mathematica}}/{{header|Wolfram Language}}==

conn=OpenSQLConnection[JDBC["ODBC(DSN)","testdb"],"Username"->"John","Password"->"JohnsPassword"];

SQLExecute[conn,"UPDATE players SET name = `1`, score = `2`, active = `3` WHERE jerseyNum = `4`", {SQLArgument["Smith, Steve",42,True,99]}]

=={{header|NetRexx}}==

Using an [http://db.apache.org/derby/ Apache Derby] embedded database:

options replace format comments java crossref symbols nobinary

method isFalse() public static returns boolean

return \isTrue

=={{header|Nim}}==

Using an SQLite in memory database and "db_sqlite" high level binding from standard library.

let db = open(":memory:", "", "", "")

echo row

{{out}}

=={{header|Objeck}}==

use ODBC;

};

}

=={{header|Pascal}}==

{{libheader|SQLite}}

Tested with Free Pascal 2.6.4 (arm) and SQLite 3.8.7.1

uses

sqlite3, sysutils;

// Close the database connection.

sqlite3_close(db);

{{out}}

<pre>

=={{header|Perl}}==

my $db = DBI->connect('DBI:mysql:mydatabase:host','login','password');

$statment = $db->prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");

=={{header|Phix}}==

{{libheader|SQLite}}

<span style="color: #000080;font-style:italic;">--

-- demo\rosetta\Parameterized_SQL_statement.exw

<span style="color: #000000;">sqlite3_close</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>

{{out}}

<pre>

=={{header|PHP}}==

"WHERE `jerseyNum` = ?";

$dbh = new PDO( "mysql:dbname=db;host=localhost", "username", "password" );

// alternatively pass parameters as an array to the execute method

$updateStatement = $dbh->prepare( $updatePlayers );

=={{header|PicoLisp}}==

As PicoLisp uses normal function calls for DB manipulations, parameters are always treated as plain data and are not executed.

(put!> P 'name "Smith, Steve")

(put!> P 'score 42)

=={{header|PureBasic}}==

Procedure CheckDatabaseUpdate(database, query$)

Print(#CRLF$ + #CRLF$ + "Press ENTER to exit"): Input()

CloseConsole()

Sample output:

=={{header|Python}}==

{{trans|Ruby}}

db = sqlite3.connect(':memory:')

# and show the results

for row in db.execute('select * from players'):

outputs

<pre>(u'Smith, Steve', 42, 1, 99)

{{works with|PostgreSQL}}

{{libheader|sql db-lib}}

#lang racket/base

(require sql db)

'("Smith, Steve" 42 #t 99))

=={{header|Raku}}==

(formerly Perl 6)

my $db = DBIish.connect('DBI:mysql:mydatabase:host','login','password');

my $update = $db.prepare("UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");

=={{header|Ruby}}==

Using the {{libheader|sqlite3-ruby}} gem

[[Category:SQLite]]

db = SQLite3::Database.new(":memory:")

# and show the results

outputs

<pre>["name", "score", "active", "jerseyNum"]

=={{header|Run BASIC}}==

{{incorrect|Run BASIC|Executing a NON-parameterized update DML. This solution is exactly the opposite of the task. This example is what is explicitly warned in the task.}}

#mem execute("CREATE table players (name, score, active, jerseyNum)")

#mem execute("INSERT INTO players VALUES ('Jones, Bob',0,'N',99)")

print name$;chr$(9);score;chr$(9);active$;chr$(9);jerseyNum

WEND

<pre>Output

Smith, Steve 42 TRUE 99

===Using [http://slick.lightbend.com/doc/3.2.3/introduction.html Slick] FRM===

{{Out}}Best seen running in your browser [https://scastie.scala-lang.org/fJKRDaydSsGGlZQXJUhvxw Scastie (remote JVM)].

import slick.sql.SqlProfile.ColumnOption.SqlType

} yield n), Duration.Inf)

=={{header|Seed7}}==

A column from a result row is retrieved with the function [http://seed7.sourceforge.net/libraries/sql_base.htm#column%28in_sqlStatement,in_integer,attr_integer%29 column].

include "sql_base.s7i";

execute(testDb, "drop table players");

close(testDb);

{{out}}

=={{header|SQL}}==

{{works with|Oracle}}

VARIABLE P_NAME VARCHAR2(20);

commit;

{{Out}}

<pre>SQL> SQL>

{{works with|Db2 LUW}}

The following example is indeed parameterized SQL with named placeholders and it prevents SQL injections, and the SQL performs very well, because the execution plan is also precompiled.

--#SET TERMINATOR @

SELECT * FROM PLAYERS @

Output:

<pre>

=={{header|Tcl}}==

{{works with|Tcl|8.6}}

# These next two lines are the only ones specific to SQLite

# With apologies to http://xkcd.com/327/

setPlayer $db 76 -> "Robert'; DROP TABLE players--" 0 false

=={{header|Wren}}==

{{libheader|Wren-sql}}

An embedded program using our SQLite wrapper.

var db = Connection.open("rc.db")

System.print("\nAfter update:\n")

{{out}}