#mysqli_real_escape_string

[fromdevcom]

SQL injection is one of the most common security vulnerabilities that web developers face. It occurs when malicious users input harmful SQL code into your application's query, which can lead to unauthorized access, data breaches, and other security risks. If you are a PHP developer, preventing SQL injection should be a top priority. In this article, we will discuss effective ways to safeguard your PHP applications from SQL injection attacks. 1. Use Prepared Statements and Parameterized Queries One of the most effective ways to prevent SQL injection in PHP is by using prepared statements. Prepared statements ensure that SQL queries are executed in a controlled manner, where user input is treated strictly as data and not executable code. Example using MySQLi: phpCopy code Example using PDO: phpCopy code Why it works: Prepared statements keep the SQL logic separate from user input, thereby eliminating the possibility of injecting harmful code. 2. Escape User Input While prepared statements are the best defense, if you must use direct queries for any reason, always escape user input using the mysqli_real_escape_string() or addslashes() function. Example: phpCopy code

[topiafree]

Sades 7.1 does not support this platform

Fixed bug #81252 (PDO_ODBC doesn't account for SQL_NO_TOTAL).Fixed bug #81327 (Error build openssl extension on php 7.4.22).Fixed bug #74544 (Integer overflow in mysqli_real_escape_string()).Fixed bug #51498 (imagefilledellipse does not work for large circles).Fixed bug #80849 (HTTP Status header truncation).Fixed bug #81305 (Built-in Webserver Drops Requests With "Upgrade" Header).Fixed bug #66719 (Weird behaviour when using get_called_class() with call_user_func()).Fixed bug #72595 (php_output_handler_append illegal write access).Fixed bug #81420 (ZipArchive::extractTo extracts outside of destination).Fixed bug #81351 (xml_parse may fail, but has no error code).Fixed bug #78819 (Heap Overflow in msg_send).Fixed bug #81400 (Unterminated string in dns_get_record() results).Fixed bug #71542 (disk_total_space does not work with relative paths).Fixed bug #81407 (shmop_open won't attach and causes php to crash).Fixed bug #81353 (segfault with preloading and statically bound closure).Fixed bug #53580 (During resize gdImageCopyResampled cause colors change).Fixed bug #73122 (Integer Overflow when concatenating strings).Fixed bug #81346 (Non-seekable streams don't update position after write).Fixed bug #81302 (Stream position after stream filter removed).Fixed bug #77978 (Dirname ending in colon unzips to wrong dir).Fixed bug #81490 (ZipArchive::extractTo() may leak memory).Fixed bug #70962 (XML_OPTION_SKIP_WHITE strips embedded whitespace).Fixed bug #81475 (stream_isatty emits warning with attached stream wrapper).Fixed bug #80663 (Recursive SplFixedArray::setSize() may cause double-free).Fixed bug #81026 (PHP-FPM oob R/W in root process leading to privilege escalation) (CVE-2021-21703).Fixed bug #61700 (FILTER_FLAG_IPV6/FILTER_FLAG_NO_PRIV|RES_RANGE failing).

Fixed bug #78987 (High memory usage during encoding detection).

Fixed bug #79576 ("TYPE *" shows unhelpful message when type is not defined).

Fixed bug #81433 (DOMElement::setIdAttribute() called twice may remove ID).

Fixed bug #79971 (special character is breaking the path in xml function).

Fixed bug #54340 (Memory corruption with user_filter).

Fixed bug #81424 (PCRE2 10.35 JIT performance regression).

Fixed bug #81494 (Stopped unbuffered query does not throw error).

Fixed bug #76167 (mbstring may use pointer from some previous request).

Fixed bug #81500 (Interval serialization regression since 7.3.14 / 7.4.2).

Fixed bug #81518 (Header injection via default_mimetype / default_charset).

Getting Started Introduction A simple tutorial Language Reference Basic syntax Types Variables Constants Expressions Operators Control Structures Functions Classes and Objects Namespaces Enumerations Errors Exceptions Generators Attributes References Explained Predefined Variables Predefined Exceptions Predefined Interfaces and Classes Context options and parameters Supported Protocols and Wrappers Security Introduction General considerations Installed as CGI binary Installed as an Apache module Session Security Filesystem Security Database Security Error Reporting User Submitted Data Hiding PHP Keeping Current Features HTTP authentication with PHP Cookies Sessions Dealing with XForms Handling file uploads Using remote files Connection handling Persistent Database Connections Command line usage Garbage Collection DTrace Dynamic Tracing Function Reference Affecting PHP's Behaviour Audio Formats Manipulation Authentication Services Command Line Specific Extensions Compression and Archive Extensions Cryptography Extensions Database Extensions Date and Time Related Extensions File System Related Extensions Human Language and Character Encoding Support Image Processing and Generation Mail Related Extensions Mathematical Extensions Non-Text MIME Output Process Control Extensions Other Basic Extensions Other Services Search Engine Extensions Server Specific Extensions Session Extensions Text Processing Variable and Type Related Extensions Web Services Windows Only Extensions XML Manipulation GUI Extensions Keyboard Shortcuts ? This help j Next menu item k Previous menu item g p Previous man page g n Next man page G Scroll to bottom g g Scroll to top g h Goto homepage g s Goto search

[blogdeprogramacion]

Importar un Excel a MySql con PHP

Importar un Excel a MySql con PHP aparece primero en nuestro https://jonathanmelgoza.com/blog/importar-un-excel-a-mysql-con-php/

¿Necesitas importar masivamente información a mysql? ¿Necesitas agregar esta funcionalidad a tus proyectos web? Hoy vamos a ver un ejemplo práctico para importar un excel a mysql con php y la libreria SpreadSheet Reader, serán archivos de excel xls o xlsx y no formatos conflictivos como CSV.

Muchas veces en nuestros proyectos web o sistemas en la nube manejamos pequeños catálogos de información.

Nuestros usuarios deben de llenar la información al comenzar a utilizar su sistema lo cual se vuelve una tarea un poco molesta para ellos.

Es por esto que nos suelen pedir algun tipo de ayuda o función para que ellos llenen un excel y el sistema sea capaz de leer la información.

Hoy vamos a ver como importar un excel a mysql con php y SpreadSheet Reader.

Requerimientos

Antes de comenzar necesitamos descargarnos la librería de SpreadSheet Reader:

Web Oficial

Mi servidor

Obviamente también necesitaremos:

Servidor PHP

Excel

Teniendo los requerimientos vamos primeramente con la pantalla para seleccionar el formato excel.

Solicitar formato de excel

Necesitamos una pantalla para permitir a los usuarios de nuestro sistema subir el formato excel necesario con la información.

Para esto haremos uso de un formulario que manda la información via POST a un archivo php llamado subirFormatoProveedores.php que veremos más adelante.

Toma nota de los nombres de los input y que solo permitimos subir archivos xls y xlsx.

<div id="areaSubirFormato"> <form action="database/subirFormatoProveedores.php" method="post" enctype="multipart/form-data"> <p> <input type="submit" name="submit" value="Subir" accept=".xls,.xlsx" /> <input type="file" name="file" /> </p> </form> <p> <a href="files/formato-proveedores.xlsx" download> <i class="fa fa-download" aria-hidden="true"></i> Descargar formato </a> </p> </div>

Como puedes ver en el segundo parrafo incluyo un enlace de descarga para que puedan descargar el formato vacio con los nombres de las columnas, te sugiero hacer lo mismo.

Dejo en tus manos añadir algo de estilo a esta precaria interfaz, para no agregar codigo innecesario no pondré CSS en este tutorial.

Procesar información del excel con SpreadSheet Reader

En nuestro archivo subirFormatoProveedores.php lo primero que haremos será incluir nuestro archivo de conexion a base de datos o en su defecto realizar la conexión a MySql.

También necesitaremos incluir el archivo excel_reader2.php y SpreadSheetReader.php de nuestra librería para leer archivos Excel que descargamos anteriormente.

require_once('excel/php-excel-reader/excel_reader2.php'); require_once('excel/SpreadsheetReader.php');

Posteriormente haremos algunas tareas básicas:

if( isset($_POST["submit"]) ){ $error = false; $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']; if(in_array($_FILES["file"]["type"],$allowedFileType)){

Y subimos el archivo:

$ruta = "formatos/" . $_FILES['file']['name']; move_uploaded_file($_FILES['file']['tmp_name'], $ruta);

Inicializamos SpreadSheetReader:

$Reader = new SpreadsheetReader($ruta);

Y comenzamos lo bueno:

$sheetCount = count($Reader->sheets()); for($i=0;$i<$sheetCount;$i++) $Reader->ChangeSheet($i); $primera = true; foreach ($Reader as $Row) // Evitamos la primer linea if($primera) $primera = false; continue; // Obtenemos informacion $campo1= ""; if(isset($Row[0])) $campo1= mysqli_real_escape_string($conn,$Row[0]); $campo2= ""; if(isset($Row[1])) $campo2= mysqli_real_escape_string($conn,$Row[1]); ... .. . // Guardamos en base de datos // En siguiente sección

Bastante fácil no crees?

Vamos a ir leyendo fila por fila y preparamos la información para su inserción en base de datos, lo cual haremos ahora mismo en el siguiente tema.

Guardando los registros a base de datos

Ahora la parte que quedo pendiente, guardar en base de datos cada fila que vamos leyendo de nuestro excel.

// Guardamos en base de datos if (!empty($campo1) || !empty($campo2)) $query = "insert into tabla(campo1,campo2, ...) values('".$campo1."','".$campo2."',...)"; $result = mysqli_query($conn, $query); if (empty($result)) $error = true;

Como puedes ver esta es la parte más fácil de este post sobre como importar un excel a mysql y mira que no ha sido dificil.

Perfecto! Hemos terminado.

Si este post te fue de utilidad no olvides compartirla en tus redes sociales o dejarnos un comentario si tienes alguna duda respecto a importar un excel a mysql con php y SpreadSheet Reader.

Hasta luego!

[mizzle1]

Quick comparison between PDO and MySQL

1) MySQLi supports only MySQL database where as PDO supports different database systems like Microsoft SQL Server, MySQL, PostgreSQL etc.

2) PDO supports named paramaters which makes it flexible and easier. MySQLi doesn’t support named parameters. MySQLi provides question mark parameter binding and we must keep track of paramater order.

3) PDO::quote() not only escapes the string, but it also quotes it. In MySQLi, mysqli_real_escape_string() will only escape the string. you need to manually apply the quotes.

4) PDO has a dedicated function to get single column value PDOStatement::fetchColumn Where as MySQLi doesn’t have any.

5) Error Handling: PDO has a built in class to handle the errors. PDO::errorInfo() returns an array of error information about the last operation performed.

6) Both PDO and MySQLi supports prepared queries which helps preventing SQL injection security issues.

7) Both PDO and MySQLi offer an object-oriented API, but MySQLi also offers a procedural API – which makes it easier for newcomers to understand. On the other hand, once you master PDO, you can use it with any database.

To know more about it, visit our website.

[t-baba]

How to Connect PHP to MySQL

Do you want ot learn how to connect php to MySQL and perform different types of database operations? In this article, we'll do just that—we'll discuss MySQL database connectivity in PHP.

PHP provides different ways to connect PHP to a MySQL database server. Until PHP 5.5, one of the most popular ways was with the MySQL extension—it provided a "procedural" way to connect to the MySQL server. However, this extension is deprecated as of PHP 5.5, so we’re not going to discuss that.

The current two ways you can choose from are PDO and MySQLi. The PDO (PHP Data Objects) extension supports different types of underlying database servers along with the MySQL server. And thus, it’s portable in case you decide to swap the underlying database server at any point in the future. On the other hand, the MySQLi extension is specific to the MySQL server and provides better speed and performance. The MySQLi extension might also be a little simpler to understand at first, since it lets you write SQL queries directly. I've you've worked with SQL databases before, this be very familiar. On the other hand, the PDO extension creates a powerful mapping from the SQL database to your PHP code and lets you do a lot of database operations without needing to know the details of SQL or the database. In the long run and for larger projects, this can save a lot of coding and debugging effort.

In this post, I'll use the MySQLi extension. But you can learn more about the PDO extension here on Envato Tuts+.

PHP

Quickly Build a PHP CRUD Interface With the PDO Advanced CRUD Generator Tool

Sajal Soni

We’ll cover the following topics in this article:

creating a MySQL connection

inserting, updating and deleting Records

retrieving records

Best PHP Database Scripts on CodeCanyon

In this post, I'll be showing you how to make a bare-metal connection to a MySQL database from PHP. This is an important skill to have, but if you want to save time on your next project, you might want to upgrade to a professional database script. This will save time and make your work easier.

Explore the best and most useful PHP database scripts ever created on CodeCanyon. With a low-cost one time payment, you can purchase these high-quality WordPress themes and improve your website experience for you and your visitors.

Here are some of the best PHP database scripts available on CodeCanyon for 2020.

PHP

Comparing PHP Database Abstraction Layers and CRUD Plugins

Sajal Soni

Create a MySQL Connection

In this section, I'll show you how you can connect to the MySQL server from your PHP script and create a connection object. Later on, this connection object will be used to run queries, fetch output and manipulate the database records. As we discussed earlier, we’re going to use the PHP MySQLi extension for this article.

The MySQLi extension provides two different ways to perform database operations. You can use it either the object-oriented way or the procedural way, but as this tutorial is aimed at beginners, I'll stick with the procedural way. If you're interested in seeing some object oriented snippets, you can ping me in the comment section below and I’ll be happy to provide them.

To start, go ahead and create the db_connect.php file under your document root with the following contents.

<?php $mysqli_link = mysqli_connect("{HOST_NAME}", "{DATABASE_USERNAME}", "{DATABASE_PASSWORD}", "{DATABASE_NAME}"); if (mysqli_connect_errno()) { printf("MySQL connection failed with the error: %s", mysqli_connect_error()); exit; }

Note that I've used placeholders in the mysqli_connect function—you have to replace these with actual values.

Let’s go through the each placeholder to see what it stands for.

{HOST_NAME}: This represents your MySQL server’s host-name or IP address. If you have installed the MySQL server on the same system along with PHP, you should use localhost or 127.0.0.1. On the other hand, if you’re using a MySQL server which is hosted externally, you can use the corresponding host-name or IP address.

{DATABASE_USERNAME}: This represents the username of your MySQL user. Basically, this is the username which you use to connect to your MySQL server.

{DATABASE_PASSWORD}: This represents the password of your MySQL user. Again, this is the password which you use to connect to your MySQL server along with the MySQL username.

{DATABASE_NAME}: This is a name of the MySQL database which you want to connect to. Once the connection is created, you'll query this database for further operations.

Go ahead and replace these placeholders with actual values. In my case, I’ve installed the MySQL server locally, and I’ve a MySQL user with tutsplus-demo-user as the MySQL username and tutsplus-demo-password as the MySQL password. And with that, the above example looks like this:

<?php $mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database"); if (mysqli_connect_errno()) { printf("MySQL connection failed with the error: %s", mysqli_connect_error()); exit; }

If you run this example, it should create a database connection link and assign it to the $mysqli_link variable, which we’ll use later on to perform different types of database operations.

On the other hand, if there’s any problem setting up the connection, the  mysqli_connect_errno function will return an error code and the mysqli_connect_error function will display the actual error. In the above example, we’ve used it for debugging purposes.

Now, we’ve a successful connection to the MySQL server and we’ll see how to use it to perform different types of queries next section on wards.

How to Insert Records

In the previous section, we discussed how to use the mysqli_connect function to setup a database connection with the MySQL server. In this section, we’ll go ahead and discuss how to use the connection object to perform the INSERT queries.

If you want to follow along with the examples discussed in this article, you'll need to create the following MySQL table in your database. It's the table which we're going to use in all the examples from now on.

CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Go ahead and create the above MySQL table by using the phpMyAdmin software or command line tool.

Next, let’s create the db_insert.php file with the following contents. Please don’t forget to replace the connection parameters with your own.

<?php $mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database"); if (mysqli_connect_errno()) { printf("MySQL connection failed with the error: %s", mysqli_connect_error()); exit; } $insert_query = "INSERT INTO students(`first_name`,`last_name`,`email`) VALUES ('". mysqli_real_escape_string($mysqli_link, 'John') ."','". mysqli_real_escape_string($mysqli_link, 'Wood') ."','". mysqli_real_escape_string($mysqli_link, 'john.wood@tutsplus.com') ."')"; // run the insert query If (mysqli_query($mysqli_link, $insert_query)) { echo 'Record inserted successfully.'; } // close the db connection mysqli_close($mysqli_link); ?>

First, we set up a database connection by using the mysqli_connect function as we discussed earlier. After that, we’ve prepared the insert query which we’re going to execute later on. It's important to note that we've used the mysqli_real_escape_string function to escape string values that we’re going to use in the insert query. Specifically, you must use this function when you’re dealing with values submitted via $_POST variables to avoid SQL injection.

Finally, we’ve used the mysqli_query function, which takes two arguments. The first argument is the active connection link where the query will be executed. And the second argument is the MySQL query which we want to execute. The mysqli_query function returns TRUE if the query was executed successfully.

Finally, we’ve used the mysqli_close function to close the active database connection. It’s a good practice to close the database connection once you’re done with database operations.

Go ahead and run the script, and that should insert a record in the students table!

How to Update Records

Updating the records in a database from the PHP script is very similar to the insert operation with the only difference is that the query is going to be the update query instead of the insert query.

Let’s revise the above example and update the first_name field as shown in the following example.

<?php $mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database"); if (mysqli_connect_errno()) { printf("MySQL connection failed with the error: %s", mysqli_connect_error()); exit; } $update_query = "UPDATE students SET `first_name` = '". mysqli_real_escape_string($mysqli_link,'Johnny') ."' WHERE `email` = 'john.wood@tutsplus.com'"; // run the update query If (mysqli_query($mysqli_link, $update_query)) { echo 'Record updated successfully.'; } // close the db connection mysqli_close($mysqli_link); ?>

Go ahead and run the script, and that should update the record in the students table.

How to Select Records

In the earlier sections, we discussed how you can insert and update records in a database from the PHP script. In this section, we’ll explore how you can fetch records from a database by using the different types of MySQLi functions.

Firstly, you need to use the mysqli_query function to execute the select query. Upon successful execution of the select query, the mysqli_query function returns the mysqli result object which we could use to iterate over the records returned by the select query. When it comes to fetching and iterating over the records from the MySQLi result object, there are different functions available.

mysqli_fetch_all: It allows you to fetch all result rows at once. You can also specify whether you want results as an associative array, a numeric array or both.

mysqli_fetch_array: It allows you to retrieve one row at a time. And thus, you’ll have to use the while loop to iterate over all the records. Again, you can specify whether you want a result row as an associative array, a numeric array or both.

mysqli_fetch_assoc: It fetches a result row one at a time as an associate array.

mysqli_fetch_object: It fetches a result row one at a time as an object.

Let’s have a look at the following example to understand how it works:

<?php $mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database"); if (mysqli_connect_errno()) { printf("MySQL connection failed with the error: %s", mysqli_connect_error()); exit; } $select_query = "SELECT * FROM students LIMIT 10"; $result = mysqli_query($mysqli_link, $select_query); while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { echo "First Name:" . $row['first_name'] . "<br/>"; echo "Last Name:" . $row['last_name'] . "<br/>"; echo "Email:" . $row['email'] . "<br/>"; echo "<br/>"; } // close the db connection mysqli_close($mysqli_link); ?>

As you can see, we’ve used the mysqli_fetch_array function with the MYSQLI_ASSOC option as a second argument. And thus, it returns the result row as an associate array. Had you would have used the MYSQLI_NUM option, you would have accessed it like $row[0], $row[1] and $row[2] in the above example.

On the other hand, if you would use the mysqli_fetch_object function in the above example, you can access the values as shown in the following snippet. For brevity, I’ll only include the while loop snippet.

… … while ($row = mysqli_fetch_object($result)) { echo "First Name:" . $row->first_name . "<br/>"; echo "Last Name:" . $row->last_name . "<br/>"; echo "Email:" . $row->email . "<br/>"; echo "<br/>"; } ... ...

So in this way, you can fetch and display records from the MySQLi database.

How to Delete Records

In this section, we’ll see how to run delete queries from the PHP script. Deleting records from a database is a pretty straightforward operation since you just need to prepare the delete query and run it with the mysqli_query function.

Let’s go through the following example to see how it works.

<?php $mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database"); if (mysqli_connect_errno()) { printf("MySQL connection failed with the error: %s", mysqli_connect_error()); exit; } $delete_query = "DELETE FROM students WHERE `email` = 'john.wood@tutsplus.com'"; // run the update query If (mysqli_query($mysqli_link, $delete_query)) { echo 'Record deleted successfully.'; } // close the db connection mysqli_close($mysqli_link); ?>

As you can see, everything is the same as we’ve seen it already with insert and update examples except that the query is a delete query in this case.

So that's how you can perform different types of database operations from the PHP script.

And with that, we’ve reached the end of this tutorial. What we’ve discussed so far today should help you to strengthen database connectivity concepts, specifically how to connect PHP to MySQL and perform different types of operations.

Conclusion

In this article, we discussed how to connect PHP to MySQL database by using the MySQLi extension. From setting up a database connection to executing different types of queries, we discussed almost every aspect of the database connectivity.

Although I’ve tried to keep things as simple as possible, if you still find anything confusing or want to know any specific thing in detail, feel free to post your queries using the feed below.

The Best PHP Scripts on CodeCanyon

Explore thousands of the best and most useful PHP scripts ever created on CodeCanyon. With a low-cost one time payment, you can purchase these high-quality WordPress themes and improve your website experience for you and your visitors.

Here are a few of the best-selling and up-and-coming PHP scripts available on CodeCanyon for 2020.

PHP

14 Best PHP Event Calendar and Booking Scripts

Kyle Sloka-Frey

PHP

10 Best PHP URL Shortener Scripts

Monty Shokeen

PHP

12 Best Contact Form PHP Scripts

Nona Blackman

PHP

Comparing the 5 Best PHP Form Builders

Nona Blackman

PHP

Create Beautiful Forms With PHP Form Builder

Ashraff Hathibelagal

by Sajal Soni via Envato Tuts+ Code https://ift.tt/2HcGa0z

[tmaclucien]

Recently, we are going to discover a new web area: server-side web development. It reminds me of my elective course: server-side web development and database, which was a nightmare for me last semester. Because I did not know any computer languages before and it was really difficult for a web starter who started learning the server-side language firstly.

Basically, the computer languages used in clientside are HTML/CSS/JavaScript.

While PHP/ Python/ Java are the normal server-side scripting languages. But PHP is the most popular one, which I learned last semester. And I also use MySQL, which is a popular open-source relational database management system.

In MySQL, you can use SQL language to insert information to the tables you have created. And then use PHP language to create home page, login page...

But how PHP and MySQL work?

For example, if users click the login button, his username will be transferred into the server and the server will check the database if there is a same name. If yes, then check the password again. If the username and passport are correct, users then are allowed to access to the main page. If not, the login page will remind users that the usernames do not exist. 

And the following is my login page from last semester.

<?php  session_start(); // If user click login button, those information from html form will be assigned to variables//   if (isset($_POST['login'])) {    include_once("connect.php");

   $username = strip_tags($_POST['username']);    $password = strip_tags($_POST['password']);

   $username = stripslashes($username);    $password = stripslashes($password);

   $username = mysqli_real_escape_string($db, $username);    $password = mysqli_real_escape_string($db, $password);

   $password = md5($password);

//Draw all data from table "Blogging" if the username wrote by user is the same with database and assign them to a variable//    $sql = "SELECT * FROM user WHERE username = '$username' LIMIT 1";    $query = mysqli_query($db, $sql);    $row = mysqli_fetch_array($query);    $ID = $row['ID'];    $db_password = $row['password'];    $admin= $row['admin']; // If user type the right login information, they can access to home page, otherwise there will be a warning to inform user that they type the incorrect information//    if ($password == $db_password) {       $_SESSION['username'] = $username;       $_SESSION['ID'] = $ID;       if ($admin == 1) {       $_SESSION['admin'] = 1;       }       header("Location: index.php");    }  else {         echo "You didn't enter the correct detalis!";       }

 }

?>

<!DOCTYPE html> <html> <head> <title>Login</title> </head> <body background="bg.jpg"> <link rel="stylesheet" type="text/css"href="style.css">

<div class='Loginbox'> <div align="center">

<!-- The Title of Login Page --> <h1>Hello Griffith</h1> </div>

<div align="center"> <br>     <form action="login.php" method="post" enctype="multipart/form-data">      Username: <input type="text"  name="username" autofocus/>      <br><br>      Password: <input type="password"  name="password" />      <br><br>      <input type="submit" name="login" value="Login" />      <br>      <?php      echo "No Accounts? <a href = 'register.php'>Register</a>";      ?> </form> </div> </div>

</body> </html>

[airman7com]

PHP - SQL Injection

PHP – SQL Injection

Pada tutorial ini akan dibahas mengenai apa itu SQL injection, cara melakukannya dan cara pencegahannya. Meskipun dalam bab ini dijelaskan cara melakukan teknik ini, akan tetapi penulis bukan bertujuan untuk mendidik orang untuk menjadi penjahat dalam dunia virtual, melainkan memberikan pemahaman supaya orang yang membaca menjadi eling lan waspodo.

Apa itu SQL Injection?

SQL injection merupakan…

[chrisbeckstrom]

I love APIs

While I was sitting in the waiting room at the hospital (and the surgeon was taking Claire’s gall bladder out!) I wrote a little PHP to connect to my blog via the WordPress API and insert new entries into my DIY private journal. Everything I know I learned from the internet, so I probably do tons of stuff wrong… But it works! I also feel a desire to share stuff back out to the internet, to sort of return the favor. At some point I’ll figure out how to get some sort of git integration into my blog. Until then, I’ll just share code like this.

This script makes two REST API calls that returns JSON, then I iterate through the JSON to put together a post for my journal.

<?php // STREAM (wordpress rest API v2) -> JOURNAL script // why? // - connect to my blog/stream/website and get new posts // - import those posts into the journal, as long as they don't already exist // - save EVERYTHING in the journal, even though it's also in the stream/blog/website // // based on lastfm.php API script by me print "<h1>STREAM -> JOURNAL </h1>"; include 'config.php'; include 'connect.php'; include 'functions.php'; writeToLog("-------- STREAM -> JOURNAL script started -----"); ini_set('display_errors', 1); ini_set('display_startup_errors',1); // set the URL to use $number = 100; $streamurl = "https://chrisbeckstrom.com/stream/wp-json/wp/v2/posts?per_page=$number&_embed"; print "using this URL to load posts:<br>"; print "<a href='$streamurl'>" . $streamurl . "</a>" . "<br>"; writeToLog("using this URL to access the API:"); writeToLog($streamurl); // call the url $filelocation = $streamurl; $json = file_get_contents($filelocation); $json = json_decode($json, true); print "starting foreach...<br>"; foreach ($json as $value => $item) $id = $item['id']; // get the names of all the tags for this post $tagurl = "https://chrisbeckstrom.com/stream/wp-json/wp/v2/tags?post=$id"; print "loading tags from WP...<br>"; print "using <a href='$tagurl'>$tagurl</a><br>"; $rawjson = file_get_contents($tagurl); $json = json_decode($rawjson, true); // load all the tags into a list $tags = ''; foreach ($json as $value => $tagitem) print "slug:". $tagitem['slug'] . "<br>"; $tags = $tags . $tagitem['slug'] . ", "; // remove trailing , if there $tags = rtrim($tags, ' '); $tags = rtrim($tags, ','); print "tags: $tags <br>"; // wrangle the date $date = $item['date']; $date = date("Y-m-d H:i:s", strtotime($date)); // TODO: get the tags // permalink $url = $item['link']; // title of the entry (many are untitled!) $title = $item['title']['rendered']; // the HTML rendered content of the entry $content = $item['content']['rendered']; // featured image permalink (direct url, not media page) // **many entries have no featured image** $featuredImage = $item['_embedded']['wp:featuredmedia'][0]['source_url']; print "<hr>"; if($featuredImage) //only show featured image if there is one print "featured image: "; print "<img src=$featuredImage>"; print "id: $id<br>date: $date<br>link: $link<br>title: $title<br>featured: $featuredImage<br>content: $content<hr>"; // set some variables $social = "blog"; $type = "html"; $source = "stream_to_journal.php"; // date is already set, above // assemble the entry $text = ''; if($title) // if there is a title, show it $text = $title . "<br>"; if($featuredImage) // if there is a featured image, show it $text = $text . "<a href='$featuredImage'><img src=$featuredImage></a><br>"; if($content) // if there is content, show it $text = $text . $content; // escape text $text = mysqli_real_escape_string($con,$text); // connect to the DB if (mysqli_connect_errno()) echo "failed to connect to MySQL:" . mysqli_connect_error(); // check to see if this entry already exists print "<b>checking database for existing entry</b><br>"; $findEntrySQL = "SELECT * FROM entries WHERE date = '".$date."' AND social = '".$social."' and SOURCE = '".$source."'"; print "query: <br>"; print "<pre>$findEntrySQL</pre><br>"; $findEntryResult = mysqli_query($con, $findEntrySQL); $count = mysqli_num_rows($findEntryResult); print "count in the db for that datetime: $count <br>"; if ($count > 0 ) print "found an entry already! skipping <br><hr>"; else print "no existing entry found. <b>ADDING THIS TO THE DB</b><br>"; $sql="INSERT INTO entries (date,text,tags,social,type,source,url) VALUES ('$date', '$text','$tags','$social','$type','$source','$url')"; print "insert query: <pre>$sql</pre><br>"; writeToLog("insert query:"); writeToLog($sql); // actually add to the DB mysqli_query($con,$sql); print "<hr>";

  - - - (original: https://chrisbeckstrom.com/stream/2018/04/05/i-love-apis/)

[askanything-online-blog]

Using mysqli_real_escape_string() with other sanitization functions

Using mysqli_real_escape_string() with other sanitization functions

I have this function to cleanup user inputs before saving:

function test_input($data){ $data = htmlspecialchars($data); $data = stripslashes($data); $data = trim($data); return $data; }

I would like to add mysqli_real_escape_string() into the function:

function test_input($data){ $data = mysqli_real_escape_string($data); $data = htmlspecialchars($data); $data =…

[sql-sh]

Les injections SQL sont des failles de sécurités en informatique qui consiste à contourner le fonctionnement normal des requêtes SQL en utilisant un contenu spécifique.

L'article suivant est très instructif et didactique. Il donne un exemple concret d'une faille de sécurité grâce à une injection SQL.

L'article présente 3 fonctions PHP différentes :

Une fonction utilisant mysqli_real_escape_string() pour PHP 5

Une fonction utilisant mysql_real_escape_string() pour PHP 4

Une fonction utilisant addslashes() pour les versions de PHP inférieur à PHP 4.3

[t-baba]

Get Started With CRUD Operations in PHP MySQL Databases

In this article, we're going to explore how you could use a MySQL database to perform CRUD (create, read, update, and delete) operations with PHP. If you want to get your hands dirty with database connectivity in PHP, this article is a great starting point.

If you are just getting started with PHP, you probably realize that database connectivity is an essential feature that you'll need to get familiar with sooner or later. In most cases, a database is the backbone of any web application and holds the data of the application. So, as a PHP developer, you'll need to know how to deal with database operations.

In this article, we'll keep things simple and explore how to use the core mysqli functions. In upcoming articles of this series, we'll explore a couple of other ways to handle database connectivity.

Today, we'll go through the basics of database connectivity in PHP, and we'll use the MySQL database as our database back-end. Let's have a quick look at what we'll cover in this article:

how to set up a database connection

how to select a database

how to insert and update records

how to fetch records

how to delete records

I assume that you've a working installation of PHP and MySQL, and that you're aware of the basics of MySQL. Also, you'll need to make sure that the mysqli extension is enabled in your PHP installation, since we'll use it to do database operations with the MySQL database.

If you are not sure about the mysqli extension, you can check it using the phpinfo() function. In the phpinfo() output, you can check if there's a section titled mysqli. You should also see the MysqlI Support | enabled header if the mysqli extension is enabled.

There's also an easy way to check it using the CLI interface. You can run the following command to list all extensions that are enabled with your PHP set up.

$php -m

It should print a list of extensions, and if it contains the mysqli keyword, the mysqli extension is enabled.

Now that we're set up, let's start creating a database connection.

How to Set Up a Database Connection

In this section, we'll discuss how you can establish a database connection using the mysqli extension.

There are two ways you can use the mysqli extension, procedural and object-oriented, but we'll use the procedural way in this post to keep things simple. If you're curious about the object-oriented syntax, let me know your questions in the comment section and I'll be happy to answer them.

Firstly, let's go through the syntax of the mysqli_connect function which is used to set up a connection with the MySQL back-end.

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}"); ?>

The mysqli_connect function takes four arguments and returns the connection object upon successful connection. Let's go through the each argument:

MYSQL_HOSTNAME: This should be replaced with the MySQL server's host-name or IP address. If you're working with the MySQL server in your local system, you can either use localhost or 127.0.0.1.

MYSQL_USERNAME: The username of your MySQL user.

MYSQL_PASSWORD: The password of your MySQL user.

MYSQL_DATABASE: The database that you want to connect to.

Upon successful connection, the $connection_obj contains the connection object. With this, you're ready to run queries against the database which was provided to the MYSQL_DATABASE argument.

On the other hand, if the connection is not successful, we can debug it as shown in the following snippet:

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}"); if (!$connection_obj) { echo "Error No: " . mysqli_connect_errno(); echo "Error Description: " . mysqli_connect_error(); exit; } ?>

In the next section, we'll see how you can select a specific database using the connection object.

How to Select a Database

In the previous section, we discussed how to set up a database connection using the mysqli_connect function. In this section, we'll look at how to select a database once the MySQL connection is successful.

Of course, as we've already seen, you can always pass a database in the fourth argument of the mysqli_connect function itself. But, there's also another way you could do this in case you want to change a database after you've connected to MySQL. You can use the mysqli_select_db function to select a database to work with.

Let's revise the example discussed in the previous section to see how it can work.

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}"); if (!$connection_obj) { echo "Error No: " . mysqli_connect_errno(); echo "Error Description: " . mysqli_connect_error(); exit; } mysqli_select_db($connection_obj, "{MYSQL_DATABASE}"); ?>

As you can see, we've passed only three arguments in the mysqli_connect function, and that should give us a successful database connection. Instead of passing a database in the fourth argument, we select the database using the mysqli_select_db function.

The mysqli_select_db function takes two arguments: the connection object and the  database you want to connect to.

How to Create (Insert) and Update Records

In the previous sections, we discussed how to set up a MySQL connection and select a database to work with. Now, we'll look at how to execute different types of queries against the selected database. First, let's look at how to insert and update records.

If you want to follow along with examples discussed in this article, you'll need to create the following MySQL table in your database. It's the table which we're going to use in all the examples form now on.

CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, `phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Executing the above command in your MySQL database should create the employee table.

How to Create Records

To keep things simple, we'll initialize values that need to be inserted into the employee table in the beginning of the script. However, in most of the cases, these would come from user input in the $_POST variables submitted using a form.

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}"); if (!$connection_obj) { echo "Error No: " . mysqli_connect_errno(); echo "Error Description: " . mysqli_connect_error(); exit; } // initialize variables for the insert query $name = 'John Smith'; $email = 'john.smith@yahoo.com'; $phone = '541-754-1234'; // prepare the insert query $query = "INSERT INTO employee(`name`,`email`, `phone`) VALUES ('". mysqli_real_escape_string($connection_obj, $name) ."','". mysqli_real_escape_string($connection_obj, $email) ."','". mysqli_real_escape_string($connection_obj, $phone) ."')"; // run the insert query mysqli_query($connection_obj, $query); // close the db connection mysqli_close($connection_obj); ?>

In the above script, we've first initialized variables that we’re going to use in the insert query. Next, we've prepared the insert query and assigned it into the $query variable.

It's important to note that we've used the mysqli_real_escape_string function to escape string values that we’re going to use in the insert query. You must use this function when you’re dealing with string values submitted via $_POST variables. This ensures that your users don't inject malicious code into to your database queries. 

Finally, we ran the insert query using the mysqli_query function. The mysqli_query function is used to run a query against the active database. To wrap things up, we used the mysqli_close function to close the active connection.

How to Update Records

In the previous section, we discussed how you can insert new records in the MySQL table. In this section, we'll see how you can update a record which already exists in the table.

I assume that there's already a record exists in the employee table, and the id of that record is 1. In the following example, we're going to update a record which has a value of 1 in the id column.

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}"); if (!$connection_obj) { echo "Error No: " . mysqli_connect_errno(); echo "Error Description: " . mysqli_connect_error(); exit; } // initialize variables for the insert query $id = 1; $phone = '333-555-4444'; // prepare the insert query $query = "UPDATE employee SET `phone` = '". mysqli_real_escape_string($connection_obj, $phone) ."' WHERE `id` = '". (int) $id ."'"; // run the insert query mysqli_query($connection_obj, $query); // close the db connection mysqli_close($connection_obj); ?>

As you can see in the above example, we want to update the phone column of the employee table where id is 1. This is very similar to the insert query, except that we're using UPDATE instead of INSERT.

How to Retrieve Records

So far, we've discussed how to insert and update records in the MySQL table. In this section, we'll see how to retrieve (fetch) records from the MySQL table.

Take a look at the following example.

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}"); if (!$connection_obj) { echo "Error No: " . mysqli_connect_errno(); echo "Error Description: " . mysqli_connect_error(); exit; } // prepare the select query $query = "SELECT * FROM employee"; // execute the select query $result = mysqli_query($connection_obj, $query) or die(mysqli_error($connection_obj)); // run the select query while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) { echo "ID:" . $row['id'] . "<br/>"; echo "Name:" . $row['name'] . "<br/>"; echo "Phone:" . $row['phone'] . "<br/>"; echo "Email:" . $row['email'] . "<br/>"; echo "<br/>"; } // close the db connection mysqli_close($connection_obj); ?>

The first thing that you need to do in order to fetch records from a database is to execute the select query using the mysqli_query function. Upon successful execution of the select query, the mysqli_query function returns the mysqli result object, and that's what we've stored in the $result variable above.

Next, we iterate over the result set using the mysqli_fetch_array function in a while loop. The mysqli_fetch_array function fetches a single row at a time from the mysqli result set. 

The mysqli_fetch_array function takes two arguments—the result object and result type. The result type indicates what type of array will be returned from the mysqli_fetch_array function. It could be numeric, associative or both. It's an optional argument and you can pass one of these three values—MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. MYSQLI_ASSOC indicates that you want to access columns by their name, and MYSQLI_NUM indicates that you want to access columns by their column number.

If you pass the MYSQLI_NUM value in the second argument of the mysqli_fetch_array function, you can access columns as $row[0], $row[1] and so on. In our case, we've passed the MYSQLI_BOTH value, so we can access columns in both ways. If you want to access only associative values, you can use the mysqli_fetch_assoc function instead.

In the next and last section, we'll see how to delete records from a database.

How to Delete Records

In this section, we'll see how to delete records from a database.

Take a look at the following example.

<?php $connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}"); if (!$connection_obj) { echo "Error No: " . mysqli_connect_errno(); echo "Error Description: " . mysqli_connect_error(); exit; } // initialize variables for the delete query $id = 1; // prepare the insert query $query = "DELETE FROM employee WHERE `id` = '". (int) $id ."'"; // run the delete query mysqli_query($connection_obj, $query); // close the db connection mysqli_close($connection_obj); ?>

As you can see, it works pretty much the same as the insert and update queries. The mysqli_query function executes the DELETE query, and that eventually deletes the record from the employee table.

Conclusion

In this article, we explored how you can use the MySQL database with PHP. This article was intended to provide you with a basic knowledge of MySQL database connectivity in PHP to beginners. We saw how to go from setting up a database connection to executing different types of queries.

Feel free to post your feedback and queries using the feed below.

by Sajal Soni via Envato Tuts+ Code https://ift.tt/2QhD0yM