Asked  7 Months ago    Answers:  5   Viewed   28 times

I am new to using prepared statements in mysql with php. I need some help creating a prepared statement to retrieve columns.

I need to get information from different columns. Currently for a test file, I use the completely unsecure SQL statement:

$qry = "SELECT * FROM mytable where userid='{$_GET['userid']}' AND category='{$_GET['category']}'ORDER BY id DESC"
$result = mysql_query($qry) or die(mysql_error()); 

Can someone help me create a secure mysql statement using input from url parameters (as above) that is prepared?

BONUS: Prepared statements are suppose to increase speed as well. Will it increase overall speed if I only use a prepared statement three or four times on a page?

 Answers

38

Here's an example using mysqli (object-syntax - fairly easy to translate to function syntax if you desire):

$db = new mysqli("host","user","pw","database");
$stmt = $db->prepare("SELECT * FROM mytable where userid=? AND category=? ORDER BY id DESC");
$stmt->bind_param('ii', intval($_GET['userid']), intval($_GET['category']));
$stmt->execute();

$stmt->store_result();
$stmt->bind_result($column1, $column2, $column3);

while($stmt->fetch())
{
    echo "col1=$column1, col2=$column2, col3=$column3 n";
}

$stmt->close();

Also, if you want an easy way to grab associative arrays (for use with SELECT *) instead of having to specify exactly what variables to bind to, here's a handy function:

function stmt_bind_assoc (&$stmt, &$out) {
    $data = mysqli_stmt_result_metadata($stmt);
    $fields = array();
    $out = array();

    $fields[0] = $stmt;
    $count = 1;

    while($field = mysqli_fetch_field($data)) {
        $fields[$count] = &$out[$field->name];
        $count++;
    }
    call_user_func_array(mysqli_stmt_bind_result, $fields);
}

To use it, just invoke it instead of calling bind_result:

$stmt->store_result();

$resultrow = array();
stmt_bind_assoc($stmt, $resultrow);

while($stmt->fetch())
{
    print_r($resultrow);
}
Tuesday, June 1, 2021
 
kwhohasamullet
answered 7 Months ago
19

It uses the C API, so in this case there is no chance for SQL injection.

Wednesday, March 31, 2021
 
ChronoFish
answered 9 Months ago
73

NO, A prepared statement would not be a solution because it is not possible to bind the table name. So avoid to use prepared statement for Truncate Table.

You cannot bind any SQL literal but data one. So keywords, operators and any identifier can not be bind using prepared statement. You can only bind data.

PDO prepared statements are useful when running queries with user input as they allow you to use features such as bound parameters to sanitise user input.

So In my suggestion you should not use prepared statement for truncate table.

If you really want to truncate using prepared , In case of Opencart which you are using, Use the code:

$sql = sprintf('TRUNCATE TABLE %s%s', DB_PREFIX, $table);
$this->db->query($sql); 

try with this once and let me know

Wednesday, March 31, 2021
 
Angolao
answered 9 Months ago
97

Because you're not inserting ALL columns, you need to dynamically build an insert statement that will specify the columns you're inserting into.

First, create an array of the columns you want to use. Use this both to generate your form and to retrieve the values

$exclude = array("id", "nameandsurname", "departument", "phone", "computer", "date");
$result = mysql_query("SHOW COLUMNS FROM employees") or   die(mysql_error());
$columns = array();
while ($row = mysql_fetch_array($result)) {
    if (!in_array($row[0], $exclude) {
        $columns[] = $row[0];
    }
}

Render your form from the $columns array:

foreach ($columns as $column) {
    echo '<tr><td bgcolor="#ddddff">'.$column.'<br />';
    if (stripos($column, "privileges") !== false) {
        echo '<p><a class="hint" href="#">
                <input type="text" name="'.$column.'">
                <span>Privileges like "occupation" or "like  someone"</span></a>';
    } else {
        echo '<select name="'.$column.'">
                <option value = "No">No
                <option value = "Yes">Yes
              </select>';
    }
    echo '</td></tr>';
}

Then, dynamically build your INSERT string from the posted values for those columns. Be sure to protect against SQL injection:

$keys = array();
$values = array();
foreach ($columns as $column) {
    $value = trim($_POST[$column]);
    $value = mysql_real_escape_string($value);
    $keys[] = "`{$column}`";
    $values[] = "'{$value}'";
}
$query = "INSERT INTO 'employees' (" . implode(",", $keys) . ") 
          VALUES (" . implode(",", $values) . ");";

Note: this will work better if you select from INFORMATION_SCHEMA.COLUMNS so that you can know the type of column you're inserting into. That way, you won't have to quote everything.

Saturday, May 29, 2021
 
ajreal
answered 7 Months ago
26

The discussion thus far has been about protecting from SQL Injection and Persistent cross site scripting. It sounds like you're on the right track.

  • Your use of prepared statements is a "best practice" to combat SQL injection.
  • htmlspecialchars() is a good start to prevent XSS, but you have to escape data in the encoding scheme that is appropriate to where you are outputting data. OWASP has a comprehensive page that discusses this: XSS (Cross Site Scripting) Prevention Cheat Sheet. The short answer: Ensure you are using "the escape syntax for the part of the HTML document you're putting untrusted data into."
Saturday, May 29, 2021
 
Elxx
answered 7 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share