Wherefore shouldn't I usage mysql_* capabilities successful PHP?

Wherefore shouldn't I usage mysql_* capabilities successful PHP?

What are the method causes for wherefore 1 shouldn't usage mysql_* capabilities? (e.g. mysql_query(), mysql_connect() oregon mysql_real_escape_string())?

Wherefore ought to I usage thing other equal if they activity connected my tract?

If they don't activity connected my tract, wherefore bash I acquire errors similar

Informing: mysql_connect(): Nary specified record oregon listing


The MySQL delay:

  • Is not nether progressive improvement
  • Is formally deprecated arsenic of PHP 5.5 (launched June 2013).
  • Has been eliminated wholly arsenic of PHP 7.Zero (launched December 2015)
    • This means that arsenic of 31 Dec 2018 it does not be successful immoderate supported interpretation of PHP. If you are utilizing a interpretation of PHP which helps it, you are utilizing a interpretation which doesn't acquire safety issues mounted.
  • Lacks an OO interface
  • Doesn't activity:
    • Non-blocking, asynchronous queries
    • Ready statements oregon parameterized queries
    • Saved procedures
    • Aggregate Statements
    • Transactions
    • The "fresh" password authentication technique (connected by default successful MySQL 5.6; required successful 5.7)
    • Immoderate of the fresh performance successful MySQL 5.1 oregon future

Since it is deprecated, utilizing it makes your codification little early impervious.

Deficiency of activity for ready statements is peculiarly crucial arsenic they supply a clearer, little mistake-inclined technique of escaping and quoting outer information than manually escaping it with a abstracted relation call.

Seat the examination of SQL extensions.


PHP provides 3 antithetic APIs to link to MySQL. These are the mysql(eliminated arsenic of PHP 7), mysqli, and PDO extensions.

The mysql_* capabilities utilized to beryllium precise fashionable, however their usage is not inspired anymore. The documentation squad is discussing the database safety occupation, and educating customers to decision distant from the generally utilized ext/mysql delay is portion of this (cheque php.internals: deprecating ext/mysql).

And the future PHP developer squad has taken the determination to make E_DEPRECATED errors once customers link to MySQL, whether or not done mysql_connect(), mysql_pconnect() oregon the implicit transportation performance constructed into ext/mysql.

ext/mysql was formally deprecated arsenic of PHP 5.5 and has been eliminated arsenic of PHP 7.

Seat the Reddish Container?

Once you spell connected immoderate mysql_* relation handbook leaf, you seat a reddish container, explaining it ought to not beryllium utilized anymore.

Wherefore

Shifting distant from ext/mysql is not lone astir safety, however besides astir having entree to each the options of the MySQL database.

ext/mysql was constructed for MySQL Three.23 and lone received precise fewer additions since past piece largely holding compatibility with this aged interpretation which makes the codification a spot more durable to keep. Lacking options that is not supported by ext/mysql see: (from PHP handbook).

Ground to not usage mysql_* relation:

  • Not nether progressive improvement
  • Eliminated arsenic of PHP 7
  • Lacks an OO interface
  • Doesn't activity non-blocking, asynchronous queries
  • Doesn't activity ready statements oregon parameterized queries
  • Doesn't activity saved procedures
  • Doesn't activity aggregate statements
  • Doesn't activity transactions
  • Doesn't activity each of the performance successful MySQL 5.1

Supra component quoted from Quentin's reply

Deficiency of activity for ready statements is peculiarly crucial arsenic they supply a clearer, little mistake susceptible technique of escaping and quoting outer information than manually escaping it with a abstracted relation call.

Seat the examination of SQL extensions.

Suppressing deprecation warnings

Piece codification is being transformed to MySQLi/PDO, E_DEPRECATED errors tin beryllium suppressed by mounting error_reporting successful php.ini to exclude E_DEPRECATED:

error_reporting = E_ALL ^ E_DEPRECATED

Line that this volition besides fell another deprecation warnings, which, nevertheless, whitethorn beryllium for issues another than MySQL. (from PHP handbook)

The article PDO vs. MySQLi: Which Ought to You Usage? by Dejan Marjanovic volition aid you to take.

And a amended manner is PDO, and I americium present penning a elemental PDO tutorial.

A elemental and abbreviated PDO tutorial

Q. Archetypal motion successful my head was: what is `PDO`?

A. “PDO – PHP Information Objects – is a database entree bed offering a single technique of entree to aggregate databases.”

alt text

Connecting to MySQL

With mysql_* relation oregon we tin opportunity it the aged manner (deprecated successful PHP 5.5 and supra)

$link = mysql_connect('localhost', 'user', 'pass');mysql_select_db('testdb', $link);mysql_set_charset('UTF-8', $link);

With PDO: Each you demand to bash is make a fresh PDO entity. The constructor accepts parameters for specifying the database origin PDO's constructor largely takes 4 parameters which are DSN (information origin sanction) and optionally username, password.

Present I deliberation you are acquainted with each but DSN; this is fresh successful PDO. A DSN is fundamentally a drawstring of choices that archer PDO which operator to usage, and transportation particulars. For additional mention, cheque PDO MySQL DSN.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

If location is immoderate transportation mistake, it volition propulsion a PDOException entity that tin beryllium caught to grip Exception additional.

Bully publication: Connections and Transportation direction ¶

You tin besides walk successful respective operator choices arsenic an array to the 4th parameter. I urge passing the parameter which places PDO into objection manner. Due to the fact that any PDO drivers don't activity autochthonal ready statements, truthful PDO performs emulation of the fix. It besides lets you manually change this emulation. To usage the autochthonal server-broadside ready statements, you ought to explicitly fit it false.

The another is to bend disconnected fix emulation which is enabled successful the MySQL operator by default, however fix emulation ought to beryllium turned disconnected to usage PDO safely.

I volition future explicate wherefore fix emulation ought to beryllium turned disconnected. To discovery ground delight cheque this station.

It is lone usable if you are utilizing an aged interpretation of MySQL which I bash not really useful.

Beneath is an illustration of however you tin bash it:

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

Tin we fit attributes last PDO operation?

Sure, we tin besides fit any attributes last PDO operation with the setAttribute technique:

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 'username', 'password');$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Mistake Dealing with

Mistake dealing with is overmuch simpler successful PDO than mysql_*.

A communal pattern once utilizing mysql_* is:

//Connected to MySQL$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

OR die() is not a bully manner to grip the mistake since we tin not grip the happening successful die. It volition conscionable extremity the book abruptly and past echo the mistake to the surface which you normally bash NOT privation to entertainment to your extremity customers, and fto bloody hackers detect your schema. Alternately, the instrument values of mysql_* capabilities tin frequently beryllium utilized successful conjunction with mysql_error() to grip errors.

PDO provides a amended resolution: exceptions. We tin unit PDO into 1 of 3 mistake modes by mounting the mistake manner property. 3 mistake dealing with modes are beneath.

  • PDO::ERRMODE_SILENT. It's conscionable mounting mistake codes and acts beautiful overmuch the aforesaid arsenic mysql_* wherever you essential cheque all consequence and past expression astatine $db->errorInfo(); to acquire the mistake particulars.
  • PDO::ERRMODE_WARNING Rise E_WARNING. (Tally-clip warnings (non-deadly errors). Execution of the book is not halted.)
  • PDO::ERRMODE_EXCEPTION: Propulsion exceptions. It represents an mistake raised by PDO. You ought to not propulsion a PDOException from your ain codification. Seat Exceptions for much accusation astir exceptions successful PHP. It acts precise overmuch similar or die(mysql_error());, once it isn't caught. However dissimilar or die(), the PDOException tin beryllium caught and dealt with gracefully if you take to bash truthful.

Bully publication:

Similar:

$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

And you tin wrapper it successful try-catch, similar beneath:

try { //Connect as appropriate as above $db->query('hi'); //Invalid query!} catch (PDOException $ex) { echo "An Error occured!"; //User friendly message/message you want to show to user some_logging_function($ex->getMessage());}

You bash not person to grip with try-catch correct present. You tin drawback it astatine immoderate clip due. Besides it whitethorn brand much awareness to drawback it astatine extracurricular the relation that calls the PDO material:

function data_fun($db) { $stmt = $db->query("SELECT * FROM table"); return $stmt->fetchAll(PDO::FETCH_ASSOC);}//Then latertry { data_fun($db);}catch(PDOException $ex) { //Here you can handle error and show message/perform action you want.}

Besides, you tin grip by or die() oregon we tin opportunity similar mysql_*, however it volition beryllium truly diverse. You tin fell the unsafe mistake messages successful exhibition by turning display_errors off and conscionable speechmaking your mistake log.

Present, last speechmaking each the issues supra, you are most likely reasoning: what the heck is that once I conscionable privation to commencement leaning elemental SELECT, INSERT, UPDATE, oregon DELETE statements? Don't concern, present we spell:

Deciding on Information

PDO select image

Truthful what you are doing successful mysql_* is:

<?php$result = mysql_query('SELECT * from table') or die(mysql_error());$num_rows = mysql_num_rows($result);while($row = mysql_fetch_assoc($result)) { echo $row['field1'];}

Present successful PDO, you tin bash this similar:

<?php$stmt = $db->query('SELECT * FROM table');while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['field1'];}

Oregon

<?php$stmt = $db->query('SELECT * FROM table');$results = $stmt->fetchAll(PDO::FETCH_ASSOC);//Use $results

Line: If you are utilizing the technique similar beneath (query()), this technique returns a PDOStatement entity. Truthful if you privation to fetch the consequence, usage it similar supra.

<?phpforeach($db->query('SELECT * FROM table') as $row) { echo $row['field1'];}

Successful PDO Information, it is obtained through the ->fetch(), a technique of your message grip. Earlier calling fetch, the champion attack would beryllium telling PDO however you’d similar the information to beryllium fetched. Successful the beneath conception I americium explaining this.

Fetch Modes

Line the usage of PDO::FETCH_ASSOC successful the fetch() and fetchAll() codification supra. This tells PDO to instrument the rows arsenic an associative array with the tract names arsenic keys. Location are galore another fetch modes excessively which I volition explicate 1 by 1.

Archetypal of each, I explicate however to choice fetch manner:

 $stmt->fetch(PDO::FETCH_ASSOC)

Successful the supra, I person been utilizing fetch(). You tin besides usage:

Present I travel to fetch manner:

  • PDO::FETCH_ASSOC: returns an array listed by file sanction arsenic returned successful your consequence fit
  • PDO::FETCH_BOTH (default): returns an array listed by some file sanction and Zero-listed file figure arsenic returned successful your consequence fit

Location are equal much selections! Publication astir them each successful PDOStatement Fetch documentation..

Getting the line number:

Alternatively of utilizing mysql_num_rows to acquire the figure of returned rows, you tin acquire a PDOStatement and bash rowCount(), similar:

<?php$stmt = $db->query('SELECT * FROM table');$row_count = $stmt->rowCount();echo $row_count.' rows selected';

Getting the Past Inserted ID

<?php$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");$insertId = $db->lastInsertId();

Insert and Replace oregon Delete statements

Insert and update PDO image

What we are doing successful mysql_* relation is:

<?php$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());echo mysql_affected_rows($result);

And successful pdo, this aforesaid happening tin beryllium completed by:

<?php$affected_rows = $db->exec("UPDATE table SET field='value'");echo $affected_rows;

Successful the supra question PDO::exec execute an SQL message and returns the figure of affected rows.

Insert and delete volition beryllium coated future.

The supra technique is lone utile once you are not utilizing adaptable successful question. However once you demand to usage a adaptable successful a question, bash not always always attempt similar the supra and location for ready message oregon parameterized message is.

Ready Statements

Q. What is a ready message and wherefore bash I demand them?
A. A ready message is a pre-compiled SQL message that tin beryllium executed aggregate occasions by sending lone the information to the server.

The emblematic workflow of utilizing a ready message is arsenic follows (quoted from Wikipedia 3 Three component):

  1. Fix: The message template is created by the exertion and dispatched to the database direction scheme (DBMS). Definite values are near unspecified, known as parameters, placeholders oregon hindrance variables (labelled ? beneath):
`INSERT INTO PRODUCT (name, price) VALUES (?, ?)`
  1. The DBMS parses, compiles, and performs question optimization connected the message template, and shops the consequence with out executing it.
  2. Execute: Astatine a future clip, the exertion provides (oregon binds) values for the parameters, and the DBMS executes the message (perchance returning a consequence). The exertion whitethorn execute the message arsenic galore occasions arsenic it needs with antithetic values. Successful this illustration, it mightiness provision 'Breadstuff' for the archetypal parameter and 1.00 for the 2nd parameter.

You tin usage a ready message by together with placeholders successful your SQL. Location are fundamentally 3 ones with out placeholders (don't attempt this with adaptable its supra 1), 1 with unnamed placeholders, and 1 with named placeholders.

Q. Truthful present, what are named placeholders and however bash I usage them?
A. Named placeholders. Usage descriptive names preceded by a colon, alternatively of motion marks. We don't attention astir assumption/command of worth successful sanction spot holder:

 $stmt->bindParam(':bla', $bla);

bindParam(parameter,variable,data_type,length,driver_options)

You tin besides hindrance utilizing an execute array arsenic fine:

<?php$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");$stmt->execute(array(':name' => $name, ':id' => $id));$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Different good characteristic for OOP mates is that named placeholders person the quality to insert objects straight into your database, assuming the properties lucifer the named fields. For illustration:

class person { public $name; public $add; function __construct($a,$b) { $this->name = $a; $this->add = $b; }}$demo = new person('john','29 bla district');$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");$stmt->execute((array)$demo);

Q. Truthful present, what are unnamed placeholders and however bash I usage them?
A. Fto's person an illustration:

<?php$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");$stmt->bindValue(1, $name, PDO::PARAM_STR);$stmt->bindValue(2, $add, PDO::PARAM_STR);$stmt->execute();

and

$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");$stmt->execute(array('john', '29 bla district'));

Successful the supra, you tin seat these ? alternatively of a sanction similar successful a sanction spot holder. Present successful the archetypal illustration, we delegate variables to the assorted placeholders ($stmt->bindValue(1, $name, PDO::PARAM_STR);). Past, we delegate values to these placeholders and execute the message. Successful the 2nd illustration, the archetypal array component goes to the archetypal ? and the 2nd to the 2nd ?.

Line: Successful unnamed placeholders we essential return attention of the appropriate command of the components successful the array that we are passing to the PDOStatement::execute() technique.

SELECT, INSERT, UPDATE, DELETE ready queries

  1. SELECT:

    $stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");$stmt->execute(array(':name' => $name, ':id' => $id));$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

  2. INSERT:

    $stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");$stmt->execute(array(':field1' => $field1, ':field2' => $field2));$affected_rows = $stmt->rowCount();

  3. DELETE:

    $stmt = $db->prepare("DELETE FROM table WHERE id=:id");$stmt->bindValue(':id', $id, PDO::PARAM_STR);$stmt->execute();$affected_rows = $stmt->rowCount();

  4. UPDATE:

    $stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");$stmt->execute(array($name, $id));$affected_rows = $stmt->rowCount();

Line:

Nevertheless PDO and/oregon MySQLi are not wholly harmless. Cheque the reply Are PDO ready statements adequate to forestall SQL injection? by ircmaxell. Besides, I americium quoting any portion from his reply:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$pdo->query('SET NAMES GBK');$stmt = $pdo->prepare("SELECT * FROM test WHERE name = ? LIMIT 1");$stmt->execute(array(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));

For years, the mysql_ capabilities had been a communal display successful PHP codification, utilized to link to and work together with MySQL databases. Nevertheless, they person been deprecated and yet eliminated from PHP for precise bully causes. Persevering with to usage these capabilities successful contemporary PHP functions is not lone atrocious pattern however besides introduces important safety dangers. This station volition explicate wherefore you ought to debar mysql_ capabilities and what alternate options you ought to usage alternatively to guarantee your codification is unafraid, businesslike, and maintainable.

Wherefore Ought to We Chorus from Utilizing mysql_ Capabilities successful PHP?

The mysql_ delay was the first manner PHP related to MySQL databases. Nevertheless, it suffers from respective captious flaws that brand it unsuitable for contemporary net improvement. 1 of the about important points is its vulnerability to SQL injection assaults. These capabilities bash not supply constructed-successful extortion in opposition to specified assaults, that means builders had to manually sanitize enter, which was frequently achieved incorrectly oregon missed wholly, starring to terrible safety breaches. Moreover, the delay lacks activity for contemporary MySQL options and has been formally deprecated, that means it nary longer receives updates oregon safety patches.

Deficiency of Safety

Safety ought to beryllium a paramount interest for immoderate net exertion, and the mysql_ capabilities merely bash not message the essential extortion. The lack of constructed-successful extortion in opposition to SQL injection assaults means that immoderate exertion utilizing these capabilities is inherently susceptible. SQL injection happens once malicious codification is inserted into SQL queries, possibly permitting attackers to publication, modify, oregon delete information from your database. Manually escaping person enter is mistake-susceptible and hard to bash persistently, making the mysql_ capabilities a important safety hazard. Utilizing these capabilities is similar leaving the advance doorway of your exertion broad unfastened for attackers.

Nevertheless bash I revert all conception modifications palmy Git managed project to erstwhile authorities?

Show and Options

Past safety issues, the mysql_ delay lacks galore of the options and show optimizations disposable successful newer database extensions. It doesn't activity contemporary MySQL options similar ready statements, which tin importantly better show and safety. Ready statements let you to direct the SQL question construction individually from the information, lowering the hazard of SQL injection and permitting the database to optimize the question execution program. Moreover, the mysql_ delay is mostly slower and little businesslike than alternate options similar PDO oregon MySQLi, impacting the general show of your exertion. Utilizing deprecated and outdated instruments finally leads to method indebtedness and accrued care prices.

Amended Alternate options to mysql_ successful PHP

Luckily, PHP gives sturdy and unafraid alternate options to the deprecated mysql_ capabilities. The 2 capital choices are PDO (PHP Information Objects) and MySQLi (MySQL Improved Delay). Some supply important enhancements successful safety, show, and characteristic activity. PDO gives a database-agnostic interface, permitting you to control betwixt antithetic database programs with minimal codification modifications. MySQLi, connected the another manus, is particularly designed for MySQL databases and supplies entree to newer MySQL options. Selecting both of these alternate options is a important measure in direction of gathering much unafraid and businesslike PHP functions.

PDO (PHP Information Objects)

PDO supplies a accordant interface for accessing antithetic database programs. This means you tin compose codification that plant with MySQL, PostgreSQL, SQLite, and another databases with out having to larn a fresh API for all 1. PDO helps ready statements, which are important for stopping SQL injection assaults. Ready statements let you to direct the SQL question construction individually from the information, guaranteeing that person enter is handled arsenic information and not arsenic executable codification. Moreover, PDO gives a much entity-oriented attack to database action, making your codification cleaner and much maintainable. Switching to PDO is a large manner to early-impervious your exertion and better its general structure.

MySQLi (MySQL Improved Delay)

MySQLi is different fantabulous alternate to the mysql_ delay, providing improved show, safety, and characteristic activity particularly for MySQL databases. Similar PDO, MySQLi helps ready statements, offering sturdy extortion in opposition to SQL injection assaults. MySQLi besides gives some procedural and entity-oriented interfaces, permitting you to take the coding kind that champion fits your task. Moreover, MySQLi supplies entree to newer MySQL options that are not disposable successful the older mysql_ delay, specified arsenic improved quality fit dealing with and activity for saved procedures. Migrating to MySQLi permits you to return afloat vantage of the capabilities of contemporary MySQL databases.

Characteristic mysql_ PDO MySQLi
SQL Injection Extortion Guide (Susceptible) Ready Statements Ready Statements
Database Activity MySQL Lone Aggregate Databases MySQL Lone
API Procedural Entity-Oriented Procedural & Entity-Oriented
Care Deprecated (Nary Activity) Progressive Progressive

Transferring distant from mysql_ capabilities is indispensable for contemporary PHP improvement. The safety dangers, deficiency of options, and deprecated position brand it an unsuitable prime for immoderate fresh oregon present tasks. By adopting PDO oregon MySQLi, you tin physique much unafraid, businesslike, and maintainable functions. Return the clip to migrate your codification to 1 of these alternate options and defend your customers and your information. Larn much astir unafraid coding practices and OWASP tips to heighten your exertion's safety. Commencement your modulation present and guarantee your exertion is fit for the early.


Tags:
Previous Post Next Post
Share to other apps
Copy Post Link

Formulario de contacto