Viewed   127 times

What are the technical reasons for why one shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?

Why should I use something else even if they work on my site?

If they don't work on my site, why do I get errors like

Warning: mysql_connect(): No such file or directory



The MySQL extension:

  • Is not under active development
  • Is officially deprecated as of PHP 5.5 (released June 2013).
  • Has been removed entirely as of PHP 7.0 (released December 2015)
    • This means that as of 31 Dec 2018 it does not exist in any supported version of PHP. If you are using a version of PHP which supports it, you are using a version which doesn't get security problems fixed.
  • Lacks an OO interface
  • Doesn't support:
    • Non-blocking, asynchronous queries
    • Prepared statements or parameterized queries
    • Stored procedures
    • Multiple Statements
    • Transactions
    • The "new" password authentication method (on by default in MySQL 5.6; required in 5.7)
    • Any of the new functionality in MySQL 5.1 or later

Since it is deprecated, using it makes your code less future proof.

Lack of support for prepared statements is particularly important as they provide a clearer, less error-prone method of escaping and quoting external data than manually escaping it with a separate function call.

See the comparison of SQL extensions.

Sunday, September 18, 2022

Why not just do this...

define('PERMISSION_READ', 1);
define('PERMISSION_ADD',  2);

//run function
// this value would be pulled from a user's setting mysql table
$_ARR_permission = 5;

if($_ARR_permission & PERMISSION_READ) {
    echo 'Access granted.';
}else {
    echo 'Access denied.';

You can also create lots of arbitrary combinations of permissions if you use bits...

$read_only = PERMISSION_READ;

//manipulating permissions is easy...
$myrights = PERMISSION_READ;
$myrights |= PERMISSION_UPDATE;    // add Update permission to my rights
Sunday, August 21, 2022

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc 
Wednesday, August 17, 2022

Just urlencode the string desired as a filename. All characters returned from urlencode are valid in filenames (NTFS/HFS/UNIX), then you can just urldecode the filenames back to UTF-8 (or whatever encoding they were in).

Caveats (all apply to the solutions below as well):

  • After url-encoding, the filename must be less that 255 characters (probably bytes).
  • UTF-8 has multiple representations for many characters (using combining characters). If you don't normalize your UTF-8, you may have trouble searching with glob or reopening an individual file.
  • You can't rely on scandir or similar functions for alpha-sorting. You must urldecode the filenames then use a sorting algorithm aware of UTF-8 (and collations).

Worse Solutions

The following are less attractive solutions, more complicated and with more caveats.

On Windows, the PHP filesystem wrapper expects and returns ISO-8859-1 strings for file/directory names. This gives you two choices:

  1. Use UTF-8 freely in your filenames, but understand that non-ASCII characters will appear incorrect outside PHP. A non-ASCII UTF-8 char will be stored as multiple single ISO-8859-1 characters. E.g. ó will be appear as ó in Windows Explorer.

  2. Limit your file/directory names to characters representable in ISO-8859-1. In practice, you'll pass your UTF-8 strings through utf8_decode before using them in filesystem functions, and pass the entries scandir gives you through utf8_encode to get the original filenames in UTF-8.

Caveats galore!

  • If any byte passed to a filesystem function matches an invalid Windows filesystem character in ISO-8859-1, you're out of luck.
  • Windows may use an encoding other than ISO-8859-1 in non-English locales. I'd guess it will usually be one of ISO-8859-#, but this means you'll need to use mb_convert_encoding instead of utf8_decode.

This nightmare is why you should probably just transliterate to create filenames.

Monday, August 8, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :