How to include a PHP variable inside a MySQL statement

Question

I m trying to insert values in the contents table  It works fine if I do not have a PHP variable inside VALUES  When I put the variable  type inside VALUES then this doesn t work  What am I doing wrong    type    testing   mysql query  INSERT INTO contents  type  reporter  description        VALUES  type   john    whatever

User · Answer

That s the easy answer    query  SELECT   FROM CountryInfo WHERE Name       name        and you define  name whatever you want  And another way  the complex way  is like that    query     SELECT       GLOBALS  Name                          FROM CountryInfo                INNER JOIN District                ON District CountryInfoId   CountryInfo CountryInfoId                INNER JOIN City                ON City DistrictId   District DistrictId                INNER JOIN       GLOBALS  Name                       ON       GLOBALS  Name        CityId   City CityId                WHERE CountryInfo Name         GLOBALS  CountryName

User · Answer

The rules of adding a PHP variable inside of any MySQL statement are plain and simple   Any variable that represents an SQL data literal   or  to put it simply - an SQL string  or a number  MUST be added through a prepared statement  No exceptions  Any other query part  such as an SQL keyword  a table or a field name  or an operator - must be filtered through a white list   So as your example only involves data literals  then all variables must be added through placeholders  also called parameters   To do so   In your SQL statement  replace all variables with placeholders prepare the resulting query bind variables to placeholders execute the query  And here is how to do it with all popular PHP database drivers  Adding data literals using mysql ext Such a driver doesn t exist  Adding data literals using mysqli  type    testing    reporter    quot John O Hara quot    query    quot INSERT INTO contents  type  reporter  description                VALUES        whatever   quot    stmt    mysqli- gt prepare  query    stmt- gt bind param  quot ss quot    type   reporter    stmt- gt execute     The code is a bit complicated but the detailed explanation of all these operators can be found in my article  How to run an INSERT query using Mysqli  as well as a solution that eases the process dramatically  For a SELECT query you will need to add just a call to get result   method to get a familiar mysqli result from which you can fetch the data the usual way   reporter    quot John O Hara quot    stmt    mysqli- gt prepare  quot SELECT   FROM users WHERE name   quot     stmt- gt bind param  quot s quot    reporter    stmt- gt execute     result    stmt- gt get result     row    result- gt fetch assoc       or while        Adding data literals using PDO  type    testing    reporter    quot John O Hara quot    query    quot INSERT INTO contents  type  reporter  description                VALUES        whatever   quot    stmt    pdo- gt prepare  query    stmt- gt execute   type   reporter     In PDO  we can have the bind and execute parts combined  which is very convenient  PDO also supports named placeholders which some find extremely convenient  Adding keywords or identifiers Sometimes we have to add a variable that represents another part of a query  such as a keyword or an identifier  a database  table or a field name   It s a rare case but it s better to be prepared  In this case  your variable must be checked against a list of values explicitly written in your script  This is explained in my other article  Adding a field name in the ORDER BY clause based on the user s choice   Unfortunately  PDO has no placeholder for identifiers  table and field names   therefore a developer must filter them out manually  Such a filter is often called a  quot white list quot   where we only list allowed values  as opposed to a  quot black-list quot  where we list disallowed values  So we have to explicitly list all possible variants in the PHP code and then choose from them   Here is an example   orderby     GET  orderby       quot name quot      set the default value  allowed     quot name quot   quot price quot   quot qty quot       the white list of allowed field names  key   array search  orderby   allowed  true      see if we have such a name if   key     false         throw new InvalidArgumentException  quot Invalid field name quot         Exactly the same approach should be used for the direction    direction     GET  direction       quot ASC quot    allowed     quot ASC quot   quot DESC quot     key   array search  direction   allowed  true   if   key     false         throw new InvalidArgumentException  quot Invalid ORDER BY direction quot        After such a code  both  direction and  orderby variables can be safely put in the SQL query  as they are either equal to one of the allowed variants or there will be an error thrown  The last thing to mention about identifiers  they must be also formatted according to the particular database syntax  For MySQL it should be backtick characters around the identifier  So the final query string for our order by example would be  query    quot SELECT   FROM  table  ORDER BY   orderby   direction quot

User · Answer

The text inside  type is substituted directly into the insert string  therefore MySQL gets this       VALUES testing   john    whatever     Notice that there are no quotes around testing  you need to put these in like so    type    testing   mysql query  INSERT INTO contents  type  reporter  description  VALUES   type    john    whatever        I also recommend you read up on SQL injection  as this sort of parameter passing is prone to hacking attempts if you do not sanitize the data being used    MySQL - SQL Injection Prevention

User · Answer

To avoid SQL injection the insert statement with be   type    testing    name    john    description    whatever     stmt    con- gt prepare  INSERT INTO contents  type  reporter  description  VALUES               stmt- gt bind param  sss    type    name   description    stmt- gt execute

User · Answer

The best option is prepared statements  Messing around with quotes and escapes is harder work to begin with  and difficult to maintain  Sooner or later you will end up accidentally forgetting to quote something or end up escaping the same string twice  or mess up something like that  Might be years before you find those type of bugs   http   php net manual en pdo prepared-statements php

User · Answer

Here   type  testing    it s string  mysql query  INSERT INTO contents  type  reporter  description  VALUES   type    john    whatever       at that time u can use it for string     type 12   it s integer mysql query  INSERT INTO contents  type  reporter  description  VALUES  type   john    whatever       at that time u can use  type

[php] How to include a PHP variable inside a MySQL statement

Examples related to php

Examples related to mysql

Examples related to sql

Examples related to string

Examples related to variables