Bindparam

  • This flag is used generally by the internals when producing so-called “anonymous” bound expressions, it isn’t generally applicable to explicitly-named bindparam constructs. Required¶ – If True, a value is required at execution time. If not passed, it defaults to True if neither bindparam.value or bindparam.callable were passed.
  • もちろん PDO::PARAMSTR にすると文字列って事になる。まぁ文字列の場合は bindParam を使うべきだけど。 bindValue と bindParam. BindValueは値をバインドします。 bindParamは変数をバインドします。評価は実行時です。.

SQL in Web Pages. SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.

In this tutorial you will learn how to insert records in a MySQL table using PHP.

Inserting Data into a MySQL Database Table

Now that you've understood how to create database and tables in MySQL. In this tutorial you will learn how to execute SQL query to insert records into a table.

The INSERT INTO statement is used to insert new rows in a database table.

Let's make a SQL query using the INSERT INTO statement with appropriate values, after that we will execute this insert query through passing it to the PHP mysqli_query() function to insert data in table. Here's an example, which insert a new row to the persons table by specifying values for the first_name, last_name and email fields.

Example

Download

If you remember from the preceding chapter, the id field was marked with the AUTO_INCREMENT flag. This modifier tells the MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1.

Inserting Multiple Rows into a Table

Bindparam

You can also insert multiple rows into a table with a single insert query at once. To do this, include multiple lists of column values within the INSERT INTO statement, where column values for each row must be enclosed within parentheses and separated by a comma.

Let's insert few more rows into the persons table, like this:

Example

Download

Now, go to phpMyAdmin (http://localhost/phpmyadmin/) and check out the persons table data inside demo database. You will find the value for the id column is assigned automatically by incrementing the value of previous id by 1.

Note: Any number of line breaks may occur within a SQL statement, provided that any line break does not break off keywords, values, expression, etc.

Insert Data into a Database from an HTML Form

In the previous section, we have learned how to insert data into database from a PHP script. Now, we'll see how we can insert data into database obtained from an HTML form. Let's create an HTML form that can be used to insert new records to persons table.

Step 1: Creating the HTML Form

Here's a simple HTML form that has three text <input> fields and a submit button.

Step 2: Retrieving and Inserting the Form Data

When a user clicks the submit button of the add record HTML form, in the example above, the form data is sent to 'insert.php' file. The 'insert.php' file connects to the MySQL database server, retrieves forms fields using the PHP $_REQUEST variables and finally execute the insert query to add the records. Here is the complete code of our 'insert.php' file:

Example

Download

In the next chapter we will extend this insert query example and take it one step further by implementing the prepared statement for better security and performance.

Note: The mysqli_real_escape_string() function escapes special characters in a string and create a legal SQL string to provide security against SQL injection.

This is very basic example of inserting the form data in a MySQL database table. You can extend this example and make it more interactive by adding validations to the user inputs before inserting it to the database tables. Please check out the tutorial on PHP form validation to learn more about sanitizing and validating user inputs using PHP.

SQL Injection

SQL injection is a code injection technique that might destroy your database.

SQL injection is one of the most common web hacking techniques.

SQL injection is the placement of malicious code in SQL statements, via web page input.

SQL in Web Pages

SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.

Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString):

Example

txtUserId = getRequestString('UserId');
txtSQL = 'SELECT * FROM Users WHERE UserId = ' + txtUserId;

The rest of this chapter describes the potential dangers of using user input in SQL statements.

SQL Injection Based on 1=1 is Always True

Look at the example above again. The original purpose of the code was to create an SQL statement to select a user, with a given user id.

If there is nothing to prevent a user from entering 'wrong' input, the user can enter some 'smart' input like this:

UserId:

Then, the SQL statement will look like this:

The SQL above is valid and will return ALL rows from the 'Users' table, since OR 1=1 is always TRUE.

Does the example above look dangerous? What if the 'Users' table contains names and passwords?

The SQL statement above is much the same as this:

SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;

A hacker might get access to all the user names and passwords in a database, by simply inserting 105 OR 1=1 into the input field.

SQL Injection Based on '=' is Always True

Here is an example of a user login on a web site:

Username:

Password:

Example

uName = getRequestString('username');
uPass = getRequestString('userpassword');
sql = 'SELECT * FROM Users WHERE Name =' + uName + ' AND Pass =' + uPass + ''

Result

SELECT * FROM Users WHERE Name ='John Doe' AND Pass ='myPass'

A hacker might get access to user names and passwords in a database by simply inserting ' OR '=' into the user name or password text box:

User Name:

Password:

The code at the server will create a valid SQL statement like this:

Bindparam Sql

Result

SELECT * FROM Users WHERE Name =' or '=' AND Pass =' or '='

The SQL above is valid and will return all rows from the 'Users' table, since OR '=' is always TRUE.

SQL Injection Based on Batched SQL Statements

Most databases support batched SQL statement.

A batch of SQL statements is a group of two or more SQL statements, separated by semicolons.

The SQL statement below will return all rows from the 'Users' table, then delete the 'Suppliers' table.

Example

Look at the following example:

Mysqli Bind Param

Example

Bindparam $_post

txtUserId = getRequestString('UserId');
txtSQL = 'SELECT * FROM Users WHERE UserId = ' + txtUserId;

And the following input:

User id:

The valid SQL statement would look like this:

Result

SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;

Use SQL Parameters for Protection

To protect a web site from SQL injection, you can use SQL parameters.

SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.

ASP.NET Razor Example

txtUserId = getRequestString('UserId');
txtSQL = 'SELECT * FROM Users WHERE UserId = @0';
db.Execute(txtSQL,txtUserId);

Note that parameters are represented in the SQL statement by a @ marker.

The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

Another Example

txtNam = getRequestString('CustomerName');
txtAdd = getRequestString('Address');
txtCit = getRequestString('City');
txtSQL = 'INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)';
db.Execute(txtSQL,txtNam,txtAdd,txtCit);

Examples

The following examples shows how to build parameterized queries in some common web languages.

SELECT STATEMENT IN ASP.NET:

txtUserId = getRequestString('UserId');
sql = 'SELECT * FROM Customers WHERE CustomerId = @0';
command = new SqlCommand(sql);
command.Parameters.AddWithValue('@0',txtUserId);
command.ExecuteReader();

INSERT INTO STATEMENT IN ASP.NET:

txtNam = getRequestString('CustomerName');
txtAdd = getRequestString('Address');
txtCit = getRequestString('City');
txtSQL = 'INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)';
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue('@0',txtNam);
command.Parameters.AddWithValue('@1',txtAdd);
command.Parameters.AddWithValue('@2',txtCit);
command.ExecuteNonQuery();

INSERT INTO STATEMENT IN PHP:

$stmt = $dbh->prepare('INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)');
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();