mysql - How can I prevent SQL injection in PHP? -


if user input inserted without modification sql query, application becomes vulnerable sql injection, in following example:

$unsafe_variable = $_post['user_input'];   mysql_query("insert `table` (`column`) values ('$unsafe_variable')");

that's because user can input value'); drop table table;--, , query becomes:

insert `table` (`column`) values('value'); drop table table;--')

what can done prevent happening?

use prepared statements , parameterized queries. these sql statements sent , parsed database server separately parameters. way impossible attacker inject malicious sql.

you have 2 options achieve this:

  1. using pdo (for supported database driver):

    $stmt = $pdo->prepare('select * employees name = :name');  $stmt->execute(array('name' => $name));  foreach ($stmt $row) {     // $row }

  2. using mysqli (for mysql):

    $stmt = $dbconnection->prepare('select * employees name = ?'); $stmt->bind_param('s', $name);  $stmt->execute();  $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) {     // $row }

if you're connecting database other mysql, there driver-specific second option can refer (e.g. pg_prepare() , pg_execute() postgresql). pdo universal option.

correctly setting connection

note when using pdo access mysql database real prepared statements not used default. fix have disable emulation of prepared statements. example of creating connection using pdo is:

$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);

in above example error mode isn't strictly necessary, but advised add it. way script not stop fatal error when goes wrong. , gives developer chance catch error(s) thrown pdoexceptions.

what mandatory first setattribute() line, tells pdo disable emulated prepared statements , use real prepared statements. makes sure statement , values aren't parsed php before sending mysql server (giving possible attacker no chance inject malicious sql).

although can set charset in options of constructor, it's important note 'older' versions of php (< 5.3.6) silently ignored charset parameter in dsn.

explanation

what happens sql statement pass prepare parsed , compiled database server. specifying parameters (either ? or named parameter :name in example above) tell database engine want filter on. when call execute, prepared statement combined parameter values specify.

the important thing here parameter values combined compiled statement, not sql string. sql injection works tricking script including malicious strings when creates sql send database. sending actual sql separately parameters, limit risk of ending didn't intend. parameters send when using prepared statement treated strings (although database engine may optimization parameters may end numbers too, of course). in example above, if $name variable contains 'sarah'; delete employees result search string "'sarah'; delete employees", , not end an empty table.

another benefit using prepared statements if execute same statement many times in same session parsed , compiled once, giving speed gains.

oh, , since asked how insert, here's example (using pdo):

$preparedstatement = $db->prepare('insert table (column) values (:column)');  $preparedstatement->execute(array('column' => $unsafevalue));

can prepared statements used dynamic queries?

while can still use prepared statements query parameters, structure of dynamic query cannot parametrized , query features cannot parametrized.

for these specific scenarios, best thing use whitelist filter restricts possible values.

// value whitelist // $dir can 'desc' otherwise 'asc' if (empty($dir) || $dir !== 'desc') {    $dir = 'asc'; }

Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more