php with mysql

SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid. Rule 1: Never Trust User Supplied The usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id . But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what. The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this. if is_int($customer_id) { //Do all the stuff we want to do if we have a integer //submitted for ...

The MySQL Document Store and X Devapi have a lot of very interesting features but right now my programming language of choice, PHP, is not yet supported. My Python is rusty and learning Node.JS is progressing. But the ability to search data from a database without knowing Structured Query Language (SQL) is going to appeal to many. Example One import mysqlx import string session = mysqlx.get_session({ 'host': 'localhost', 'port': 33060, 'user': 'dstokes', 'password': 'Hell0Dave!'}) schema = session.get_schema('world_x'); collection = schema.get_collection('countryinfo') print "Find three records***\n" result = collection.find().limit(3).execute() docs = result.fetch_all() for i, data in enumerate(docs): print "{iteration}: {data}".format(iteration = i, data=data) print "Find USA***\n" result = collection.find('_id = "USA"').execute() row = result.fetch_all() for i,...

In the first two blogs entries on this series we set up a connection to MySQL and sent off a query. Now we need to get the data back from the database and into the application. An Embarrassment of Riches PHP has many options for what we want to do. But for the best place to start with was checking that rows were actually returned from a query. Below the results from a query are returned to a variable named $result . We can find out how many rows were returned from the server by examining $result->num_rows . if (!$result = $mysqli->query($sql)) { // Again, do not do this on a public site, but we'll show you how // to get the error information echo "Error: Our query failed to execute and here is why: \n"; echo "Query: " . $sql . "\n"; echo "Errno: " . $mysqli->errno . "\n"; echo "Error: " . $mysqli->error . "\n"; exit; } // succeeded, but do we have a result? if ( $resu...

Last time we set up a connection from a PHP program to a MySQL server. This time we will progress a little further in that direction. Query Data is asked for from the MySQL server by using a query written in a language named Structured Query Language (SQL). Now that we have a connection open to the server, we can pass out request to the server. Manual Labor The PHP Manual is wonderful 99% of time. If you take a peek at the page for mysqli::query there is a great example of a simple query. Many of learned to program by copying/pasting from books/manuals and this is a great us of the examples in the PHP manual. Except it may not work for you. MySQL is usually case SeNsATiVe, so 'A' may not be the same thing as 'a'. But this is dependent to some extent on your operating system where 'A' = 'a'. I was using the example from the manual and ... it did not work. What Happened Here is an excerpt of the code, somewhat cut down: <?php $mysqli = ne...

PHP and MySQL have had a long intertwined path together. I have been talking with a lot of newbies in the past several months who are trying to become PHP developers but are amazed at all the ancillary parts that go along with PHP such as unit testing, databases, JavaScript, continuous integration, and much more. Add in that there are two MySQL APIs -- PDO & MySQLi -- and an older deprecated mysql API still often found in the wild. This blog is the start of a series for new PHP developers to learn to program with a database. Client Server Model The PHP code when it seeks to talk to a MySQL (or most other databases) will make a connection to a port at an IP address. Usually MySQL is listening on port 3306. If you are developing an accessing a database on your local computer the IP address used will generally be at 127.0.0.1. The software that goes between the PHP application and the database is called a connector . So your code on you local system an be talking to a dat...

MySQL has had a JSON data type since version 5.7 was released way back in '15. But did you know you could produce JSON output from non-JSON columns? It is very simple and saves a lot of time over trying to format it in your application. World Database We will be using the good old World database that MySQL has used for years in documentation, examples, and in the classroom. Starting with a simple query we will build up to something more complex. SELECT Name, District, Population FROM City; This will output the data from the table in a tabular format. 'Kabul', 'Kabol', '1780000' 'Qandahar', 'Qandahar', '237500' Array or Object? We have two options for composing JSON data: JSON_ARRAY and JSON_OBJECT . Of the two, you will find JSON_ARRAY the least fussy. It will JSON-ize your data very easily. It takes a list of values or an empty list and returns a JSON array. We add this function to our example query and it becomes SELECT JSON_...