Monday, August 20, 2018

How do I prevent MySQL Injection with PHP?

We all know that you can make a simple form with some HTML, put together a quick PHP script, build a few MySQL tables, and you have yourself a full, working application in a matter of minutes.

Your PHP may end up initally looking like this...

$sql = "SELECT * FROM SomeTable WHERE id = " . $_POST['id'];

The HTML script would contain the necessary form and input elements, and SomeTable would be in the database. But this creates the possibility of a MySQL injection.

To prevent mysql injection, we send the SQL query and user input in SEPARATE statements, called Prepared Statements. Put a question mark where you want the variable to be...

$db_link = new mysqli($hostname, $username, $password, $database);
$sql = "SELECT * FROM SomeTable WHERE id = ?";
$statement = $db_link->prepare($sql);
$statement->bind_param($_POST['id']);

This will prevent users from submitting malicious data. Imagine, for example, if someone tried to submit an 'id' of value "0 AND DROP TABLES;", which would have a negative consequence on your app.

Sunday, August 19, 2018

How to Wrap Text in Pre-Tags in HTML

You can use PRE tags to make text look like code in most computer terminals, which is a monospaced, Courier font.

But the problem with PRE tags is that the text does not wrap automatically. If the user wants to see a long paragraph, they need to scroll RIGHT on their computer screen, instead of DOWN, which is more intuitive.

To make PRE tagged text autowrap, the code is very simple...

pre {
white-space: pre-wrap;
}

All PRE-tags are now wrapped. This solution will work on all modern browsers.

If you want a solution that works on older machines, the code is a bit more complex...

pre {
white-space: pre-wrap; /* css-3 */
white-space: -moz-pre-wrap; /* Mozilla, since 1999 */
white-space: -pre-wrap; /* Opera 4-6 */
white-space: -o-pre-wrap; /* Opera 7 */
word-wrap: break-word; /* Internet Explorer 5.5+ */
}

(Multi-Browser Solution Source: https://longren.io/wrapping-text-inside-pre-tags/ )

Saturday, August 18, 2018

MySQL Limit Pagination is Slow on Large Offset Quantities - How do I resolve?

With MySQL field indexing on fields you regularly use in WHERE clauses, you can speed up your performance.

You can also use LIMIT to either limit your results, or to get paginated results. If you show 20 results per page and you want page 7's results, you would select with...

MyDB@sys mysql> SELECT * FROM ... LIMIT 7, 20;
...
20 rows in set (0.01 sec)

But this query becomes intensely slow with higher offsets, such as this query...

MyDB@sys mysql> SELECT * FROM ... LIMIT 50000, 20;
...
20 rows in set (0.35 sec)

The same exact query, using different offset limits, has very different performance!

But it is very easy to get around! Just use a subquery to select the id's first, which has your LIMIT.

MyDB@sys mysql> SELECT * FROM ... WHERE id IN(SELECT id FROM ... LIMIT 50000, 20);

Bypassing this problem shouldn't really work, and yet it does! That is because this has been a bug with MySQL for about the past decade: https://bugs.mysql.com/bug.php?id=41871

Friday, August 17, 2018

How do I remove a MySQL Index on a Table and Column?

MySQL indices can make your DB faster, but at times, you may need to remove them, for instance, when they are no longer needed or when a compound index is more appropriate.

For a MySQL DB named MyDB, you can remove a table index with...

MyDB@sys mysql> DROP INDEX `SomeIndexedColumn` ON `MyTable`;

Then, you can verify that this change took place, by showing the indices on the table afterwards...

MyDB@sys mysql> SHOW KEYS FROM MyTable;

Then you can proceed to add the indices or compound indices that your table needs.

Thursday, August 16, 2018

MySQL is Not Using Index Key on Field - How do I resolve?

If you need to speed up your MySQL DB, you're adding indices on your columns. You will see a massive, immediate improvement in performance.

But sometimes you won't, and there are a number of ways to diagnose and resolve those problems.

1) Run the Analyze command on the table. For instance...

MyDB@sys mysql> ANALYZE `MyTable`

This tells your MySQL storage engine to look at the possible ways to join or select this table.

2) MySQL is using a different keys than the one you specified indices for. If your where clause is on multiple statements, you may want a compound index to make it more clear to the storage engine...

MyDB@sys mysql> ALTER TABLE `MyTable`
ADD INDEX `ComboIndex` (`Column1`,`Column2`)

And this will work with...

MyDB@sys mysql> SELECT .... WHERE
Column1 = "ABC" OR COLUMN2 = "DEF";

3) Your query may be simply too complicated for MySQL to grasp. In this case, simplifying the query into multiple UNION'd statements will produce the desired output at amazing speed...

MyDB@sys mysql> (SELECT .... WHERE
Column1 = "ABC") UNION (SELECT .... WHERE COLUMN2 = "DEF");

Wednesday, August 15, 2018

How do I add a MySQL Index Key to Improve Select Performance?

If you have a MySQL database, you can improve performance by adding index keys.

Indexing makes it so that SELECT statements will execute faster, when you have a WHERE clause that uses an indexed key. The speed different can be insignificant (on small DB's), or tremendous (on bigger DB's).

Add a MYSQL index to a column like this....

MyDB@sys mysql> ALTER TABLE `MyTable` ADD INDEX `MyColumn` (`MyColumn`);

Then you will get better performance with statements like...

MyDB@sys mysql> SELECT * FROM MyTable WHERE MyColumn = 'SomethingIWant';

You can also verify the keys a table has with....

MyDB@sys mysql> SHOW KEYS FROM MyTable;

Or, even with this....

MyDB@sys mysql> SHOW CREATE TABLE MyTable;

Tuesday, August 14, 2018

How do I list all tables in a MySQL Database?

If you have a MySQL database named MyDB, you can see all of the tables with...

MyDB@sys mysql> show tables;

If you have a bit more time, you can see all the tablenames for just the current DB with...

MyDB@sys mysql> select TABLE_NAME from INFORMATION_SCHEMA.tables WHERE TABLE_SCHEMA = 'MyDB';

Or, if you want to see all tablenames for all databases in MySQL, you can run this....

MyDB@sys mysql> select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.tables;

You're now on your way to seeing all the database information.

Monday, August 13, 2018

How to Fix Getting Errcode 28 on MySQL?

Query3 failed: Error writing file '/tmp/MY9uc2x7' (Errcode: 28)

If you are seeing this error, it is not a MySQL Error Code, but a Linux OS Error Code.

See the full message with the C++ debugging tool, perror:

perror 28

And this will output:

OS error code 28: No space left on device

So the problem is easily solved! Delete some stuff on the server hosting your DB.

Monday, August 6, 2018

How to Grep files in Linux, but only certain file extensions?


Want to search your file system on Linux?  It's easy!

    grep -r --include=\*.txt 'searchterm' ./

...or case-insensitive version...

    grep -r -i --include=\*.txt 'searchterm' ./
  • grep : command
  • -r : recursively
  • -i : ignore-case
  • --include : all *.txt: text files (escape with \ just in case you have a directory with asterisks in the filenames)
  • 'searchterm' : What to search
  • ./ : Start at current directory.

Thursday, May 30, 2013

Installing Apache, PHP, and ImageMagick together using Linux/CentOS

There is a big disclaimer that should be made to all those about to venture on ImageMagick programming using PHP: the great, vast majority of WAMP (Windows-Apache-MySQL-PHP) servers do not support ImageMagick commands at all, since it is a "different package," and there appears to be no way to install ImageMagick through standard Add/Remove Applications within Linux (as far as standard Debian linux releases go).  It's very easy to get Apache/PHP running in either Windows or Linux with pretty much any standard OS/distro supported so that you can run all of your own code on your own computer.  But that's definitely not the case when you want Imagick to cooperate with your Apache server and your PHP code!

It took me almost an entire week just to get Apache/PHP/ImageMagick working all together nice and happily so that I could run my own PHP code on my own machine (natively, too).  So, I decided to simply write down the steps, to help anyone else out who could need help.

Guide to Building an Apache/PHP/Imagick Server with Linux/CentOS 6.4:::

1. Install CentOS 6.4.  (Theoretically, other versions of CentOS should work as well, but I simply chose the most recent one.)  Also, you can very easily emulate CentOS within Windows (for instance, the unfree choice of VMWare).
2. Install PHP, PHP-Common, PHP-extensions, etc., from standard Application Package Manager ("Add/Remove Programs", usually in system tools somewhere).  (You don't have to worry about Apache, because that comes with CentOS 6.4 anyway.)
3. Open up a terminal and enter admin mode with the command "su".  You'll need a password for this.
4. Enter this command: yum install ImageMagick ImageMagick-devel
5. Enter this command: pecl install imagick
6. Modify php.ini and include the line "extension=imagick.so".  This file is located at "/etc/php.ini" normally.  The line must be exactly inserted in the section of "Dynamic Extensions", which begins with this commented out text:

--------------------------------
;;;;;;;;;;;;;;;;;;;;;;
; Dynamic Extensions ;
;;;;;;;;;;;;;;;;;;;;;;
--------------------------------

Use the "search" feature of gedit to find the text.  Add the extension below the commented out note in this section so that the result looks like:

--------------------------------
;;;;
; Note: packaged extension modules are now loaded via the .ini files
; found in the directory /etc/php.d; these are loaded by default.
;;;;

extension=imagick.so
--------------------------------

Putting the extension directly at the top caused me serious permission issues in running Imagick code.  Also, with CentOS, the permissions automatically disable user control for this php.ini.  Enter the following terminal command: "chown [YOUR-USERNAME] -R /etc/php.ini", without the quotes to get control to modify it.  Note to insert your username where it states "[YOUR-USERNAME]".
7. Enter this command: "service httpd restart", without quotes, from admin mode.  This restarts Apache/PHP/PHP's extensions now that you've installed ImageMagick.  If this gives you an error, just try "service httpd start".

My favorite part about this method is that you can update your system with issuing the "yum update" command and then the "yum upgrade" command -- and the Apache/PHP/Imagick combination will still be working perfectly!  Unlike some other guides out there for this, you don't need to rely on antiquated, unsupported versions that can be nearly impossible to find.  Also, you don't need to worry about the MySQL, Hash, MBString, Exif packages, because they all seem to be installed and properly cooperating with PHP automatically after installing PHP from the Application Manager.  This solution makes everything work well together.

The root directory of your public files is located at /var/www/html/.  Normally, this folder is not owned by the user, so you'll get a permission error when trying to add files or folders to it.  Correct that with entering admin mode in the terminal/command-line window ("su" command) and entering the following command: "chown [YOUR-USERNAME] -R /var/www/html/", without quotes.  Again, replace "[YOUR-USERNAME]" with your actual user name.  (Warning: The first time I did this, I was lazy and entered the command for only the "/var/" folder, which prevented my system ever from booting.  It would always freeze with the notorious and googleable error-message: "Could not update ICEauthority file /var/lib/gdm/.ICEauthority".  So, make sure to change ownership only for "/var/www/html/".)

To view your root directory files as compiled, resultant, PHP pages, open up a browser and enter "127.0.0.1" as the address.  So, a file in your root directory would be accessed at the following URL in your browser: http://127.0.0.1/your.file.php .  If you get a "could not connect" error message, try the command "service httpd start" at the terminal window from admin mode.  (You have to start this service manually every time the machine boots, although there's plenty of ways to automate it.)

I hope this helps!  I tried a hundred different guides on getting Apache/PHP/Imagick to work together and none of them worked.  Don't give up!  ImageMagick is a package of really neat imaging functions!