quotes string (11)

I'm trying to find out the best way to write queries. I also understand the importance of being consistent. So far I've been randomly using single quotes, double quotes and backticks without any real thought.

$query = "INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)";

Also, in the example above, consider that "table," "col[n]," and "val[n]" can be variables.

What is the standard for this? What are you doing?

I've been reading answers to similar questions for about 20 minutes, but there doesn't seem to be a definitive answer to this question.

Answers

Now suppose you are using direct post variable in MySQL query then use it like this:

$query = "INSERT INTO `table` (`id`, `name`, `email`) VALUES (" ".$_POST["id"]." ", " ".$_POST["name"]." ", " ".$_POST["email"].." ")";

This is the best practice for using PHP variables in MySQL.

Mainly in Mysql, these types of identifiers are used in ` , " , " and () queries.

" or " use to include a string as the value "01/26/2014 00:00:00" or "01/26/2014 00:00:00" . This identifier is only used for the "01/26/2014 00:00:00" string function, such as now() or sum ,max .

` use to include a table or table table, e.g. select column_name from table_name where id = "2"

() are only used to simply enclose parts of a query, for example select column_name from table_name where (id = "2" and gender = "male") or name = "rakesh.

Apart from all the (well explained) answers, there were none mentioned below and I come to this Q&A often.

In a nutshell; MySQL thinks you want to do math on your own table/column and interpret hyphens such as "email" as e mail .

Denial of responsibility. So I thought I'd add this as an "FYI" answer for those who are completely new to working with databases, and who may not understand the technical terms already described.

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you're new to PHP.)

It may be important to note that PHP treats single and double quotes differently...

Single-quoted strings are "literals" and are quite a lot of WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable replacement (backreferences in PHP are not exactly strings, they execute the command in the shell and return the result).

$foo = "bar"; echo "there is a $foo"; // There is a $foo echo "there is a $foo"; // There is a bar echo `ls -l`; // ... a directory list

If the cols tables and values ​​are variables, then there are two ways:

With double quotes "" the complete query is:

$query = "INSERT INTO $table_name (id, $col1, $col2) VALUES (NULL, "$val1", "$val2")";

$query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.") VALUES (NULL, "".$val1.", "".$val2."" )";

With single quotes "" :

$query = "INSERT INTO ".$table_name." (id, ".$col1.", ".$col2.") VALUES (NULL, ".$val1.", ".$val2.")";

Use backticks `` when the column/value name is similar to a MySQL reserved keyword.

Note. If you specify a column name with a table name, use backticks like this:

`table_name` . `column_name`<- Примечание: исключить. из задних клещей.

Backticks should be used for table and column identifiers, but are only needed when the identifier is a MySQL reserved keyword or when the identifier contains space characters or characters outside the limited set (see below). It is often recommended to avoid using reserved keywords as column or table identifiers if possible to avoid the quote problem.

Single quotes should be used for string values, such as in the VALUES() list. Double quotes are supported by MySQL for string values ​​as well, but single quotes are more widely accepted by other RDBMSs, so it's a good idea to use single quotes instead of double quotes.

MySQL also expects DATE and DATETIME literal values ​​to be single-quoted as strings, such as "2001-01-01 00:00:00" . For more information, see the Date and Time literature documentation, particularly alternatives to using the hyphen - as a segment separator in date strings.

So using your example, I would double cast the PHP string and use single quotes for the values ​​"val1", "val2" . NULL is a MySQL keyword and a non-value and is therefore not used.

None of these table or column identifiers are reserved words or use characters that require quoting, but I quoted them with backwards anyway (more on that later...).

RDBMS-related functions (such as NOW() in MySQL) should not be quoted, although their arguments are subject to the same rules or quoting rules already mentioned.

Backtick(`) table & column ┬──── ┬──┬───────┐ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ $query = " INSERT INTO `table` (`id`, `col1`, `col2`, `date`, `updated`) VALUES (NULL, "val1", "val2", "2001-01-01", NOW())"; Unquoted keyword ─────┴┴┴┘ │ │ │ │ │ │ │││││ Single-quoted (") strings ───────────┴─ ───┴── ┴────┘ │ │ │││││ Single-quoted (") DATE ──────────────────────── ───┴── Unquoted function ───────────────────────── ───────── ───────┴┴┴┴┘

Variable interpolation

Quoting patterns for variables do not change, although if you intend to interpolate variables directly in a string, it must be double quoted in PHP. Just make sure you properly escape variables for use in SQL. (It is recommended to use an API that supports prepared statements instead as a defense against SQL injection.)

// Same thing with some variable replacements // Here, a variable table name $table is backtick-quoted, and variables // in the VALUES list are single-quoted $query = "INSERT INTO `$table`(`id`, `col1`, `col2`, `date`) VALUES (NULL, "$val1", "$val2", "$date")";

Prepared Statements

When working with prepared statements, consult the documentation to determine whether the statement fillers should be included. The most popular APIs available in PHP, PDO and MySQLi involve unauthorized placeholders, like most prepared instruction APIs in other languages:

// PDO example with named parameters, unquoted $query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)" ; // MySQLi example with ? parameters, unquoted $query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

Symbols that return a back reference in identifiers:

For example:

The same can be done for table names and field names. This is very good habit if you bind your database id with back windows.

Check out this answer to learn more about reverse inferences.

Now about Double quotes & Single Quotes (Michael already mentioned this).

But to define the value, you need to use single or double quotes. Let's see another example.

INSERT INTO `tablename` (`id, `title`) VALUES (NULL, title1);

Here I deliberately forgot to wrap title1 in quotes. The server will now accept title1 as the column name (i.e. identifier). So, to indicate that this is a value, you need to use double or single quotes.

INSERT INTO `tablename` (`id, `title`) VALUES (NULL, "title1");

Now, combined with PHP, double quotes and single quotes make query writing much easier. Let's look at the modified version of the query in your question.

$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, "$val1", "$val2"");

Now, using double quotes in PHP, you will make the variables $val1 and $val2 use their values, thus creating a valid query. like

$val1 = "my value 1"; $val2 = "my value 2"; $query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, "$val1", "$val2"");

INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, "my value 1", "my value 2")

There were many helpful answers here, generally culminating in two points.

1. BACKTICKS (`) are used around identifier names.
2. SINGLE QUOTES (") are used around values.

And as @MichaelBerkowski said

Backticks should be used for table and column identifiers, but are only needed when the identifier is a MySQL reserved keyword or when the identifier contains space characters or characters outside the limited set (see below). It is often recommended to avoid using reserved keywords as column or table identifiers if possible to avoid the quote problem.

There is a case where the identifier cannot be reserved keyword or contain whitespace characters or characters outside the limited set but definitely require backlinks around them.

123E10 is a valid identifier name, but also a valid INTEGER literal.

[Without going into detail how you would get an id name like that] Let's say I want to create a temporary table called 123456e6.

No ERROR on backticks.

DB > create temporary table `123456e6` (`id` char (8)); Query OK, 0 rows affected (0.03 sec)

ERROR if you don't use callbacks.

DB > create temporary table 123451e6 (`id` char (8)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near "123451e6 (`id` char (8))" at line 1

However, 123451a6 is a fine ID name (without backticks).

DB > create temporary table 123451a6 (`id` char (8)); Query OK, 0 rows affected (0.03 sec)

This is entirely because 1234156e6 is also an exponential number.

Single quotes should be used for string values, such as in the VALUES() list.

Backticks are typically used to indicate an identifier and can also be safe due to the occasional use of reserved keywords.

When combined with PHP and MySQL, double quotes and single quotes greatly simplify query writing time.

There are two types of quotes in MySQL:

1. " to include string literals
2. ` to include identifiers such as table and column names

And then there is "this is a special case. It can be used for one of the above targets at a time depending on the server's sql_mode :

1. Default"character" can be used to nest string literals "
2. In ANSI_QUOTES mode " the symbol can be used to include identifiers, ANSI_QUOTES

The following query will produce different results (or errors) depending on the SQL mode:

SELECT "column" FROM table WHERE foo = "bar"

ANSI_QUOTES disabled

The query will select the string literal "column" where column foo is equal to the string "bar"

Enabled ANSI_QUOTES

The query will select column column where column foo is equal to column

When to use

• I suggest you avoid using " so that your code doesn't depend on SQL modes
• Always include identifiers as this is good practice (quite a few questions on SO discuss this)

There is a clear difference between the use of " " and " " .

When " " is used throughout, there is no "transformation or translation". It is printed as is.

With " ", whatever it surrounds is "translated or transformed" into its value.

What I mean by translation/conversion is this: anything contained within single quotes will not be "translated" to their values. They will be accepted because they are inside quotes. Example: a=23 , then echo "$a" will generate $a on standard output. While echo "$a" will produce 23 on standard output.

(PHP 4 >= 4.3.0, PHP 5)

mysql_real_escape_string — Escapes special characters in a string for use in an SQL statement

Description

mysql_real_escape_string (string $unescaped_string [, resource $link_identifier = NULL]): string

Escapes special characters in the unescaped_string , taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL"s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \ , " , " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

Caution

Security: the default character set

The character set must be set either at the server level, or with the API function mysql_set_charset() for it to affect mysql_real_escape_string() . See the concepts section on character sets for more information.

Parameters

unescaped_string

The string that is to be escaped.

Link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() had been called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

Return Values

Returns the escaped string, or FALSE on error.

Errors/Exceptions

Executing this function without a MySQL connection present will also emit E_WARNING level PHP errors. Only execute this function with a valid MySQL connection present.

Examples

Example #1 Simple mysql_real_escape_string() example

//Connect
$link = mysql_connect("mysql_host" , "mysql_user" , "mysql_password" )
OR die(mysql_error());

//Query
$query = sprintf ( "SELECT * FROM users WHERE user="%s" AND password="%s"",
mysql_real_escape_string($user),
mysql_real_escape_string($password));
?>

Example #2 mysql_real_escape_string() requires a connection example

This example demonstrates what happens if a MySQL connection is not present when calling this function.

The above example will output something similar to:

Warning: mysql_real_escape_string(): No such file or directory in /this/test/script.php on line 5 Warning: mysql_real_escape_string(): A link to the server could not be established in /this/test/script.php on line 5 bool(false) string(41) "SELECT * FROM actors WHERE last_name = """

Example #3 An example SQL Injection Attack

// We didn't check $_POST["password"], it could be anything the user wanted! For example:
$_POST [ "username" ] = "aidan" ;
$_POST [ "password" ] = "" OR ""="" ;

// Query database to check if there are any matching users
$query = ( $_POST [ "username" ]) " AND password=" ( $_POST [ "password" ]) "" ;
mysql_query($query);

// This means the query sent to MySQL would be:
echo $query ;
?>

The query sent to MySQL:

This would allow anyone to log in without a valid password.

Notes

A MySQL connection is required before using mysql_real_escape_string() otherwise an error of level E_WARNING is generated, and FALSE is returned. If link_identifier isn't defined, the last MySQL connection is used.

Note: mysql_real_escape_string() does not escape % and _ . These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

8 years ago

Just a little function which mimics the original mysql_real_escape_string but which doesn't need an active mysql connection. Could be implemented as a static function in a database class. Hope it helps someone.

function mysql_escape_mimic ($inp) (
if(is_array($inp))
return array_map (__METHOD__ , $inp );

If(!empty($inp ) && is_string ($inp )) (
return str_replace (array("\\" , "\0" , "\n" , "\r" , """ , """ , "\x1a" ), array("\\\\" , "\ \0" , "\\n" , "\\r" , "\\"" , "\\"" , "\\Z" ), $inp );
}

Return $inp ;
}
?>

13 years ago

Note that mysql_real_escape_string doesn"t prepend backslashes to \x00, \n, \r, and and \x1a as mentioned in the documentation, but actually replaces the character with a MySQL acceptable representation for queries (e.g. \n is replaced with the "\ n" litteral). (\, ", and " are escaped as documented) This doesn't change how you should use this function, but I think it"s good to know.

6 years ago

No discussion of escaping is complete without telling everyone that you should basically never use external input to generate interpreted code. This goes for SQL statements, or anything you would call any sort of "eval" function on.

So, instead of using this terribly broken function, use parametric prepared statements instead.

Honestly, using user provided data to compose SQL statements should be considered professional negligence and you should be held accountable by your employer or client for not using parametric prepared statements.

What does that mean?

It means instead of building a SQL statement like this:

"INSERT INTO X (A) VALUES(".$_POST["a"].)"

You should use mysqli"s prepare() function () to execute a statement that looks like this:

"INSERT INTO X (A) VALUES(?)"

NB: This doesn"t mean you should never generate dynamic SQL statements. What it means is that you should never use user-provided data to generate those statements. Any user-provided data should be passed through as parameters to the statement after it has been prepared.

So, for example, if you are building up a little framework and want to do an insert to a table based on the request URI, it"s in your best interest to not take the $_SERVER["REQUEST_URI"] value (or any part of it) and directly concatenate that with your query. Instead, you should parse out the portion of the $_SERVER["REQUEST_URI"] value that you want, and map that through some kind of function or associative array to a non-user provided value. If the mapping produces no value, you know that something is wrong with the user provided data.

Failing to follow this has been the cause of a number of SQL-injection problems in the Ruby On Rails framework, even though it uses parametric prepared statements. This is how GitHub was hacked at one point. So, no language is immune to this problem. That's why this is a general best practice and not something specific to PHP and why you should REALLY adopt it.

Also, you should still do some kind of validation of the data provided by users, even when using parametric prepared statements. This is because that user-provided data will often become part of some generated HTML, and you want to ensure that the user provided data isn't going to cause security problems in the browser.

9 years ago

There"s an interesting quirk in the example #2 about SQL injection: AND takes priority over OR, so the injected query actually executes as WHERE (user="aidan" AND password="") OR ""="", so instead of returning a database record corresponding to an arbitrary username (in this case "aidan"), it would actually return ALL database records. In no particular order. So an attacker might be able to log in as any account, but not necessarily with any control over which account it is.

Of course a attack potentialer could simply modify their parameters to target specific users of interest:

//E.g. attacker's values
$_POST [ "username" ] = "" ;
$_POST["password"] = "" OR user = "administrator" AND "" = "";

// Malformed query
$query = "SELECT * FROM users WHERE user="$_POST [ username ] " AND password=" $_POST [ password ] "" ;

echo $query ;

// The query sent to MySQL would read:
// SELECT * FROM users WHERE user="" AND password="" OR user="administrator" AND ""="";
// which would allow anyone to gain access to the account named "administrator"

?>

1 year ago

@feedr
I elaborated his note as following:
$string = "asda\0sd\x1aas\\\\\\\\dasd\"asdasd\na\"\"sdasdad";
$array1 = array("\\\\\\\\", "\0", "\n", "\r", """, """, "\x1a");
$array2 = array("\\\\\\\\\\\\\\\\\", "\\\0", "\\\n", "\\\r", "\\\" ", "\\\"", "\\\Z");
echo($string);
echo(PHP_EOL);
for($i=0; $i if ($i==0)
$p = "/(?else
$p = "/(?echo($i);
echo($p);
echo($array2[$i]);
$string = preg_replace($p, $array2[$i], $string);
echo("\t");
echo($string);
echo(PHP_EOL);
}
echo(PHP_EOL);
echo($string);

2 years ago

To Quote Sam at Numb Safari

[ "No discussion of escaping is complete without telling everyone that you should basically never use external input to generate interpreted code. This goes for SQL statements, or anything you would call any sort of "eval" function on.

So, instead of using this terribly broken function, use parametric prepared statements instead.

Honestly, using user provided data to compose SQL statements should be considered professional negligence and you should be held accountable by your employer or client for not using parametric prepared statements." ]

Sam is right........

However I do not think it is sensible to stop all sanitizing and simply pass the task on to parametric prepared statements.

A particular developer working in a particular situation will always know more about valid input (specific to that context).

If you ask a user to pass in a value you have already given them and you know that all such values ​​start AB****** and the string should be of length 7 or 11 but never any other length then you have the basis of a good pre-sanitiser - different allowable lengths of a string might indicate legacy data.

I would never want to simply pass the rubbish that a malicious user may have passed in through a form to the parametric prepared statements, I would always want to do my own sanity checks first and in some cases these may err on the side of caution and simply choose to abort the Database op completely.

That way my DB does not get clogged up with unsafe statements made safe - it simply does not get clogged up which is better.

Security in layers - sanitization and validation should still be considered in every situation BEFORE using prepared statements.

In addition as far as I can read into the official doc
==============================================

"Escaping and SQL injection

Bound variables are sent to the server separately from the query and thus cannot interfere with it. The server uses these values ​​directly at the point of execution, after the statement template is parsed. Bound parameters do not need to be escaped as they are never substituted into the query string directly"

That suggests to me that danger is avoided in the internals by alternative handling not by nullification.

This means that a large project with incomplete conversion to prepared statements, legacy code in different parts of an organization or servers talking to one another could all pass on the bad news from an immune location or situation to one that is not immune.

As long as the sanitization is competently performed without incurring additional risks then personally I would stick with certain layers of sanitization and then call the prepared statements.

First, a little about why these slashes are needed in general.
If we substitute any data into a query, then in order to distinguish this data from SQL commands, they must be placed in quotes.
For example, if you write
SELECT * FROM table WHERE name = Bill
then the database will decide that Bill is the name of another field, will not find it, and will throw an error. Therefore, the substituted data (in this case, the name Bill) must be enclosed in quotes - then the database will consider it a string, the value of which must be assigned to the name field:
SELECT * FROM table WHERE name = "Bill"
However, quotes may also appear in the data itself. Eg,
SELECT * FROM table WHERE name = "D"Artagnan"
Here the database will decide that "D" is data, and Artagnan is a command that it does not know, and will also throw an error. Therefore, it is necessary to trace all the data in order to explain to the database that the quotation marks (and some other special characters) found in them refer to the data.
As a result, we will receive a correct request that will not cause errors:
SELECT * FROM table WHERE name = "D\"Artagnan"

Thus, we found out that when substituting string data into a query, two rules should be followed:
- all inserted string data must be enclosed in quotes (single or double, but single ones are more convenient and more often used).
- special characters must be escaped with slashes.

It should be specially noted: added slashes do NOT go into the database. They are only needed in the request. When hitting the base, slashes are discarded. Accordingly, a common mistake is to use stripslashes when retrieving data from the database.

All of the above applies to string data and dates. Numbers can be inserted without trailing or surrounding them with quotation marks. If you do this then NECESSARILY! force the data to the desired type before inserting it into the query, for example:
$id = intval ($id);
However, for simplicity (and reliability), you can work with numbers as with strings (since mysql still converts them to the desired type). Accordingly, we will trace any data inserted into the request and enclose it in quotation marks.

Also, there is one more rule - optional, but it should be followed to avoid errors:
The names of fields and tables should be enclosed in back single quotes - "`" (the key with this symbol is located on a standard keyboard to the left of the "1" key). After all, the field name can coincide with mysql keywords, but if we use a back quote, then MySQL will understand everything is correct:
SELECT * FROM `table` WHERE `date` = "2006-04-04"
You should distinguish between these quotation marks and not confuse one with the other. You should also remember that backticks are not escaped by slashes.

So, we have learned how to correctly substitute data into a request.
BUT! Dynamic query construction is not limited to data substitution. Often we have to substitute SQL commands and field names into a query. And here we move on to the topic of security:

SQL Injection is a method of hacker attack when the data transferred to a script is modified in such a way that the query generated in this script begins to perform something completely different from what it was intended for.
The rules for protecting against such attacks can be divided into two points:
1. Working with data.
2. Working with query controls.

We discussed the first point in detail above. It can be said that it is not, in fact, a defense. Compliance with the rules for adding data to a query is dictated, first of all, by the requirements of SQL SYNTAX. And as a side effect, we also have protection against hacking.

The second point is much more difficult, since there is no single universal rule as for data - a backticks will not protect the field name from being modified by a hacker. It is not possible to use quotes to protect table names, SQL statements, LIMIT command parameters, and other statements.
Therefore, the basic rule when substituting control elements into a query is:
If you need to dynamically insert SQL statements or names of fields, databases, tables into a query, then under no circumstances should you insert them directly into the query.
All options for such additions must be written in ADVANCE in your script and selected based on what the user entered.
For example, if you need to pass a field name to the order by operator, then under no circumstances should you substitute it directly. We need to check it first. For example, make an array of valid values, and substitute it into the request only if the passed parameter is present in this array:
$orders =array("name" , "price" , "qty" );
$key = array_search($_GET["sort"], $orders));
$orderby = $orders [ $key ];
$query = "SELECT * FROM `table` ORDER BY$orderby " ;

We search the array of pre-described options for the word entered by the user, and if we find it, we select the corresponding element of the array. If no match is found, the first element of the array will be selected.
Thus, what is substituted into the request is not what the user entered, but what was written in our script.
The same must be done in all other cases.
For example, if the WHERE clause is dynamically generated:
if (!empty($_GET [ "price" ])) $where .= "price="" . mysql_real_escape_string ($_GET [ "price" ]). """ ;
$query = "SELECT * FROM `table` WHERE $where " ;

It’s hard for me to imagine a case where a table name can be inserted into a query dynamically, but if this happens, then the name also needs to be inserted only from a set pre-defined in the script.
The parameters of the LIMIT operator should be forced to an integer type using arithmetic operations or the intval() function.
Do not think that the examples listed here exhaust all options for dynamic query construction. You just need to understand the principle and apply it in all such cases.

Due to the nature of my work, I have to perform security audits of the source code of web applications.
Lots of web applications and a lot of code...

It's no secret that SQL injection vulnerabilities are the most common of all server-side web application vulnerabilities. There are platforms and frameworks where such things are almost completely excluded, for example ORM and so on. But statistics persistently tell us about the absolute predominance of web applications with simple concatenated SQL queries on the Internet. In addition, there are cases where ORM is generally applicable It cannot, for example, when not only the parameters of expressions, but also the query logic itself at the operator level must depend on user data.

So, let's begin.

Useless character escaping

Found in 83% of PHP web applications vulnerable to SQL injections

Using the escape function for characters such as
mysql_escape_string
mysql_real_escape_string
addslashes
without quotation marks. Most often it manifests itself in numeric parameters (all kinds of *_id).

Example

$sql = "SELECT user FROM userslist WHERE userid=".mysql_real_escape_string($_GET["uid"]);

It appears to be safe code, but only on the surface. The most common pattern of SQL injections in PHP in my practice crept in here. To attack this vulnerability, an attacker simply needs to avoid using the " " \x00 \r \n \x1a characters in the attack vector.
For example:
/index.php?uid=-777 UNION SELECT password FROM userlist

Search in code

Complicated by the semantics of the language. For a simple search you can use egrep:
egrep -Rin "(select|update|insert|delete|replace).*(from|set|into).*(mysql_escape_string|mysql_real_escape_string|addslashes)" . | grep -v "[\""]["\"]"

The logic of the search expression is as follows: find all lines in which there is no sequence of quote characters ("", "", "", "") to the left of the filtering functions. The method, of course, is far from 100%, but it is impossible to require a regular expression to perform semantic analysis.
To make it easier to display information, you can highlight the function in color in the console:
egrep -Rin "(select|update|insert|delete|replace).*(from|set|into).*(mysql_escape_string|mysql_real_escape_string|addslashes)" . | grep -v "[\""]["\"]" | egrep --color "(mysql_escape_string|mysql_real_escape_string|addslashes)"

To protect against this wildcard vulnerability, it is best to use type casting.
This always works faster and is more reliable than all kinds of filtering and screening.
For the example above, the patch could be like this:
$sql = "SELECT user FROM userslist WHERE userid=".intval($_GET["uid"]);

This concludes the short essay. I urge all web developers to try to check their sources for such designs. Better yet, expand the given search script for people.

So basically I dug deep into the areas of MySQL and PHP... specifically the security measures I should take when dealing with the database and form inputs. So far I have found the following to be highly recommended:

1. Prepared Statements
2. Using _real_escape_string()
3. NOT using magic quotes as it confuses databases and ends up giving you things like "You didn't call it...".

This is all great and I'm keeping an eye on it. However, I was wondering if I should escape characters like dollar sign [$], percent sign [%], and perhaps others. Could the query have interpreted the dollar sign as a PHP variable perhaps? What about the LIKE syntax I've heard uses the % symbol or even a wildcard? Prepared statements should technically take care of all of this, but I just wanted to be on the safe side and make sure I got everything covered properly. In cases where I forget to use prepared statements or simply neglect them, I hoped that this second line of defense could tell me that I could get rid of the dizziness.

Here's what I'm currently using for escaping:

Function escape($connection, $data)( $new_data = trim($data); $new_data = i_real_escape_string($connection, $new_data); $new_data = addcslashes($new_data, "%_$"); $new_data = htmlspecialchars ($new_data, ENT_NOQUOTES); return $new_data; )

So is this correct? Am I doing something terribly wrong? Please note that when returning the database data I will have to remove the backslashes before the $,% and _ characters.

Am I doing something terribly wrong?

First about your research.

Prepared statements – the only one wonderful thing you found.

Although using mysqli_real_escape_string (assuming you are using prepared statements) would be useless and harmful(creating a result that you noted yourself: "You called isn\t...").

And Magic Quotes have long since been removed from the language - thus not really worth anything.

So even most of your initial premises are clearly wrong.

Now to your question.

Could the query have interpreted the dollar sign as a PHP variable perhaps?

What about the LIKE syntax I've heard uses the % symbol or even a wildcard?

Yes, you heard that right. The exact purpose of the LIKE operator is to perform a pattern search. Disabling these characters in LIKE would not make the slightest sense.

Every time you are going to use the LIKE operator, you must decide which specific character to use and which to disallow. You cannot use a one-time solution. Not to mention that in all other mysql interactions the % sign has no special meaning.

Prepared statements should technically take care of all this

Prepared statements have nothing to do with $ or % signs. Prepared statements refer to SQL injection, but no character can cause it (you couldn't call "injection" a properly used LIKE operator, could you?).

Finally, to the worst part.

In case you forget to use prepared statements or simply neglect to follow them,

nothing will save you.

And the least help would be from the function you developed.

Summarize.

1. Get rid of this feature.
2. Use fillers * to represent each individual variable in the query.
3. Escape % and _ characters in the input only if they will be used in the LIKE operator and you do not want them to be interpreted.
4. Use htmlspecialchars() for output, not mysql input.

*read prepared statements if this term is unfamiliar to you.

You don't have to avoid the dollar sign. MySQL doesn't look at this character specifically, and PHP only recognizes it in source code, not in string values ​​(unless you call eval on the string, but that's a whole other worm of worms).

You would only need to escape the % and _ if you were using user input as the LIKE argument and you didn't want the user to be able to use wildcards. This may occur if you are processing a search form. You don't need to use it when storing in a database.

You don't need to use htmlspecialchars when accessing the database. This should only be used when displaying data to the user on an HTML page to prevent XSS injection.

Depending on what data and what it is used for.

If you find that PHP's default out-of-the-box statements are too large and complex, I suggest taking a look at some of the classes available on github to give you an idea of ​​simplified queries.

An example of inserting queries with this class

$data = Array ("login" => "admin", "active" => true, "firstName" => "John", "lastName" => "Doe", "password" => $db->func( "SHA1(?)",Array ("secretpassword+salt")), // password = SHA1("secretpassword+salt") "createdAt" => $db->now(), // createdAt = NOW() " expires" => $db->now("+1Y") // expires = NOW() + interval 1 year // Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear); $id = $db->insert("users", $data); if ($id) echo "user was created. Id=" . $id; else echo "insert failed: " . $db->getLastError();