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
insideVALUES
then this doesn't work. What am I doing wrong?
$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description)
VALUES($type, 'john', 'whatever')");
The rules of adding a PHP variable inside of any MySQL statement are plain and simple:
This rule covers 99% of queries and your query in particular. 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.
This approach involves four basic steps
And here is how to do it with all popular PHP database drivers:
mysqli
$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description)
VALUES(?, ?, 'whatever')";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $type, $reporter);
$stmt->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 toget_result()
method to get a familiarmysqli_result
from which you can fetch the data the usual way:
$reporter = "John O'Hara";
$stmt = $mysqli->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $reporter);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc(); // or while (...)
$type = 'testing';
$reporter = "John O'Hara";
$query = "INSERT INTO contents (type, reporter, description)
VALUES(?, ?, 'whatever')";
$stmt = $pdo->prepare($query);
$stmt->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.
Any other query part, such as SQL keyword, table or a field name, or operator - must be filtered through a white list.
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 "white list" (where we only list allowed values) as opposed to a "black-list" 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'] ?: "name"; // set the default value
$allowed = ["name","price","qty"]; // 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("Invalid field name");
}
Exactly the same approach should be used for the direction,
$direction = $_GET['direction'] ?: "ASC";
$allowed = ["ASC","DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) {
throw new InvalidArgumentException("Invalid ORDER BY direction");
}
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 bebacktick
characters around the identifier. So the final query string for our order by example would be
$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";
To avoid SQL injection the insert statement with be
$type = 'testing';
$name = 'john';
$description = 'whatever';
$con = new mysqli($user, $pass, $db);
$stmt = $con->prepare("INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $type , $name, $description);
$stmt->execute();
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.
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:
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'] .
"'";
Try This simply
$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description)
VALUES('$type', 'john', 'whatever')");
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL.
It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.