A little about linkable variables (prepared statements)

If I had to write a book about how to create unscalable Oracle applications, the first and only chapter of it would be called “Do not use bind variables”.
Tom Kite, Vice President, Oracle

Recently on Habré an article from AlexanderPHP “SQL injection for beginners appeared. Part 1". From its content and commentary to it, one may get the impression that many developers have no idea what bindable variables are, why they should be used, and what advantages they give. I will try in this article to shed a little light on these issues.

Definition of bound variables

, prepared statements, ( ; , ) — SQL- , SQL-. , :
insert into someTable(name) values(‘’);
, ? -, insert , 90% ORM; ( '') — . , , ():

$request = sql_prepare('insert into table(name) values(:1)');
/*  insert into someTable(name) values(?);*/
sql_execute($request, Array(''));

, (:1, :2,...) . , , .
insert into someTable(name) values(‘’);, , .

:
1. — , .
2. , .
3. SQL-.
.
— :

$request = sql_prepare('insert into table(name) values(:1)');
sql_execute($request, Array(''));
sql_execute($request, Array(''));
sql_execute($request, Array(''));
sql_execute($request, Array(''));

SQL- , , — .

, SQL-. , SQL- - , , . — , , . , — «» ( , ). (, , MySQL ), «» . , SQL- — . 1 — , , — . :

sql_execute("insert into table(name) values('')");
sql_execute("insert into table(name) values('')");
sql_execute("insert into table(name) values('')");
sql_execute("insert into table(name) values('')");

( ), . , «» .

. — SQL- ( , , )? ( ), prepared statements – «» sql_execute (mysql_real_query, ),
, sql_execute, . . prepared statement – . , . , , , . ( MySQL C API, PostgreSQL C library).

: — PHP PDO , SQL- . ( StackOverflow):

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

— , . . . ( — ).
, - ; , . Robert');drop table students;, , — , , .

, :

Rhaps107
mysql_real_escape_string? - ?

— mysql_real_escape_string , . . (, «» , , - ), .

@m_z21
PDO ORM . pdo , sql-.

ORM – . PDO ( MySQLi) , SQL injection , .

@VolCh21
(, ), . . mysql_* libmysql, mysqli/pdo ?

. mysqli::prepare — MySQL. , PHP. , ( ) , . « ».

, - , (prepared statements). , , . , , - , .

---

1. http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php — StackOverflow.
2. https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java — Java.
3. . Oracle . — .