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|C}}==

 

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;

 

static void Main(string[] args)

{

 

tCommand.CommandText = "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum";

 

}

}

 

=={{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|Java}}==

import java.sql.Connection;

import java.sql.PreparedStatement;

public class DBDemo{

private String protocol; //set this to some connection protocol like "jdbc:sqlserver://"

private String password;

 

PreparedStatement query;

public int setUpAndExecPS(){

"UPDATE players SET name = ?, score = ?, active = ? WHERE jerseyNum = ?");

 

 

}

 

=={{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|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|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}}

{{out}}

<pre>

</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|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)

=={{header|Racket}}==

{{works with|PostgreSQL}}

 

(define pgc

#:password "s3>r37P455"))

 

pgc

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

 

 

=={{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"]

["John Doe", "-1", "false", "100"]

["Robert'; DROP TABLE players--", "3", "true", "101"]</pre>

 

=={{header|Scala}}==

===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}}==

The library [http://seed7.sourceforge.net/libraries/sql_base.htm sql_base.s7i] provides access to databases.

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.

CREATE TABLE PLAYERS (

NAME VARCHAR(32),

 

INSERT INTO PLAYERS VALUES ('Pele', '1280', 0, 10) @

CALL UPDATE_PLAYER ('Maradona', '600', 1, 10) @

SELECT * FROM PLAYERS @

Output:

<pre>

db2 => INSERT INTO PLAYERS VALUES ('Pele', '1280', 0, 10) @

DB20000I The SQL command completed successfully.

db2 => CALL UPDATE_PLAYER ('Maradona', '600', 1, 10) @

Return Status = 0

db2 => SELECT * FROM PLAYERS @

 

=={{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

 

{{omit from|360 Assembly}}

{{omit from|6502 Assembly}}

{{omit from|6800 Assembly}}

{{omit from|80386 Assembly}}

{{omit from|DIV Games Studio}}

{{omit from|LC3 Assembly}}

{{omit from|PDP-11 Assembly}}

{{omit from|TI-83 BASIC|No network access or built-in SQL}}

{{omit from|TI-89 BASIC|No network access or built-in SQL}}

{{omit from|Unlambda|No network access or built-in SQL}}

{{omit from|XSLT 1.0}}