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

{{trans|Pascal}}

Compile with:

gcc example.c -lsqlite3

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

 

 

</pre>

 

 

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

 

 

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

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

 

 

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

Jesten, Jim 0 FALSE 100

 

=={{header|SQL}}==

{{works with|Oracle}}

 

VARIABLE P_NAME VARCHAR2(20);

commit;

 

NAME SCORE ACTIV JERSEYNUM

-------------------- ---------- ----- ----------

NAME SCORE ACTIV JERSEYNUM

-------------------- ---------- ----- ----------

 

=={{header|SQL PL}}==

{{works with|Db2 LUW}}

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 @

 

Maradona 600 1 10

 

 

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

{{omit from|PARI/GP}}

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