4

Let's pretend that I have two tables in an InnoDB database: categories and jokes; and that I'm using PHP/MySQLi to do the work. The tables look like so:

CATEGORIES
id (int, primary, auto_inc)  |  category_name (varchar[64])
============================================================
1                               knock, knock

JOKES
id (int, primary, auto_inc)  |  category_id (int)  | joke_text (varchar[255])
=============================================================================
empty

Thanks to a previous answer on here, I discovered that you could do the following in order to add a new joke comprised of: $joke_text, $category_id.

INSERT INTO jokes (category_id, joke_text)
SELECT c.id, '$joke_text'
FROM categories AS c WHERE c.id = $category_id;

This enables me, without the use of foreign keys, to be sure that the $category_id value refers to an existing category (please ignore the issue of foreign keys, as my question is aimed at helping me learn "complicated" prepared statements).

So that worked just fine. However, I am now trying to learn prepared statements and, after spending all day on it, I finally have the basics down. Unfortunately, I have ABSOLUTELY NO IDEA how to to execute the above SQL query with prepared statements, under mysqli, and I have not been able to find any info online regarding such an issue.

If anyone can help me out, I'd be very appreciative.

Community
  • 1
  • 1
EleventyOne
  • 7,300
  • 10
  • 35
  • 40

2 Answers2

3

First you create the statement very much like a normal statement you have made 

$stmt = $mysqli->prepare("INSERT INTO jokes (category_id, joke_text)
SELECT c.id, ?
FROM categories AS c WHERE c.id = ?;");

Get the statement bound to the parameter 's' stands for string data and i for integer

$stmt->bind_param('si', $joke_text,$category_id);   // bind to the parameters

/* execute prepared statement */

$stmt->execute();
skv
  • 1,793
  • 3
  • 19
  • 27
  • Isn't escaping the POST data with the mysqli real_escape_string function defeating the purpose of using prepared statements? You use prepared statements to skip that step. Else you it would be like calling the addslashes function and then escaping it with the real_escape_string function. Or that's at least what I read when first using MySQLi before moving to PDO. See [http://stackoverflow.com/questions/6232084/is-mysql-real-escape-string-necessary-when-using-prepared-statements](http://stackoverflow.com/questions/6232084/is-mysql-real-escape-string-necessary-when-using-prepared-statements) – Touch Aug 23 '13 at 02:52
  • I agree, its just a habit :) that can be removed safely – skv Aug 23 '13 at 02:55
  • Double escaping might end up giving you two single quotes when one was sent. That's where I see the problem of the security. It's best we advise that the habit is removed. :) – Touch Aug 23 '13 at 02:57
  • Hmm, perhaps this seemed a lot more complicated than it actually was. However, wouldn't I also want to put a `?` for the `WHERE c.id = $category_id` statement? So it would be: `WHERE c.id = ?`, and then I'd just add `$category_id` as a second param on `stmt->bind_param`? – EleventyOne Aug 23 '13 at 03:04
  • Not necessary, you want to do that only with parameters being added from external sources, this is getting data from db – skv Aug 23 '13 at 03:05
  • But `$category_id` is from the user, not from the db. Is it not possible to get a MySQL injection from it? – EleventyOne Aug 23 '13 at 03:10
  • My personal opinion is to use the 
    WHERE c.id = ?
    and then bind it since it's a variable. I guess I just don't like to take chances. **Edit:** Since it's a variable, the I suppose you bind it.     – Touch Aug 23 '13 at 03:11
  • Edited, sorry about ignoring it as from db – skv Aug 23 '13 at 03:13
-1

I was actually writing some code but I realised that you were searching online for this too. So instead I got you the link to the PHP website where you can see many of these.

Prepared statements

mysqli in general

Since you are learning MySQLi, why not just move to PDO instead. It's very similar but I think that's where the future of this is headed.

http://php.net/manual/en/book.pdo.php

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

Touch
  • 1,481
  • 10
  • 19