-2

So I'm using PHP to run a SQL command, using PHP variables which get input from an HTML form.

I'm essentially creating a "to-do" list, so the user submits some information on the HTML form, then an SQL command is created, and this SQL command is then stored into a "to-do" table. If the to-do is approved, then the SQL command is stored in the SQL table is then executed.

My issue here is that I am trying to access some of the PHP variables, but in order to successfully store an SQL statement within another one I have to use the double quotes for the text being passed in, otherwise, it just shows the PHP variable.

This is my code:

$ownername = trim(filter_input(INPUT_POST, 'ownername'));
$owner = trim(filter_input(INPUT_POST, 'street'));
$city = trim(filter_input(INPUT_POST, 'city'));
$country = trim(filter_input(INPUT_POST, 'country'));
$paying = trim(filter_input(INPUT_POST, 'paying'));
$base = trim(filter_input(INPUT_POST, 'base'));
$country = trim(filter_input(INPUT_POST, 'country'));
$gps = trim(filter_input(INPUT_POST, 'gps'));

$query = "INSERT INTO changes (change_id, change_sql_1, change_sql_2, change_sql_3, change_sql_4, change_sql_5, change_note) VALUES (default, 'INSERT INTO owner (owner_id, name, street, city, country) VALUES (default, '$ownername', '$treet', '$city', '$country')', 'INSERT INTO payer (loss_payee_id, loss_payee_name) VALUES (default, '$paying')', '', '', '','')";

I used an echo statement to see what the output looks like, but the SQL statement should contain double quotes ("") around each of the PHP variables. So the PHP variable text will be inside "". Example, if $ownername = 'Jimmy', then the statement being saved should be "Jimmy".

sameera lakshitha
  • 1,925
  • 4
  • 21
  • 29
datasci
  • 61
  • 1
  • 8
  • 2
    **Your code is vulnerable to SQL injection and will be hacked** even if [you are escaping inputs!](https://stackoverflow.com/a/5741264/2595450) Use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. – Spoody Apr 08 '18 at 16:18
  • @MehdiBounya How long do prepared statements sit on the server for? Can those be stored in a table? I thought the best approach was to store the SQL query that will be executed in a table so that after the user approves the to-do list, those commands then execute – datasci Apr 08 '18 at 16:27
  • You mean, you are storing a whole query? that's a really bad approach... just store the item you want and mark it as pending. And use Prepared Statements to run queries to eliminate SQL injection attacks. – Spoody Apr 08 '18 at 16:32
  • @MehdiBounya So here is the process. A user enters data into the user form. An SQL statement is then created using the different data variables from the user form. This SQL statement is then stored into the "TODO LIST" database. One column stores this newly created SQL query. Then the to-do that was created shows up in a list, so that the user can approve or delete the request on the to-do list. If he approves the to-do, then the associated query that was created and stored is then run. – datasci Apr 08 '18 at 16:34
  • 2
    Why all this trouble? just store directly to the TODO list table and add a column that says `approved` that takes `0` or `1`, when the user approves the item, you change `approved` to `1` – Spoody Apr 08 '18 at 16:37
  • @MehdiBounya well the statement being stored does many things. The statement is updating a few tables. That's why I thought storing the query in a table, waiting for approval was the best approach. I guess if I used a prepared statement, then I would have to have some type of statement that evaluates when the user accepts the to-do – datasci Apr 08 '18 at 16:41
  • I think you are misunderstanding prepared statements, _the statement is updating a few tables_: when the user approves the item, you update the tables then... – Spoody Apr 08 '18 at 16:43
  • @mehdibounya the variables are now even stored within double quotes, so even if someone entered an SQL statement into the user input, how would that execute if its being stored as a string in SQL – datasci Apr 08 '18 at 16:55
  • He can simply enter `' SQLCODEHERE` and bam, read the link in my first comment. To get rid of the SQL injection risk just use prepared statements. – Spoody Apr 08 '18 at 17:02
  • @mehdibounya but if im using javascript to validate the userform, i dont see how that could happen – datasci Apr 08 '18 at 17:04
  • 2
    Well I can see how that could happen... The user disables JavaScript and Bam (again..) – Spoody Apr 08 '18 at 17:09
  • I'm not arguing with you on the prepared statements, I need to read more about that. But because the variables are being stored inside double quotes, ' SQLCODEHERE does not work in this case. I tried, and the query is saved as such ''' SELECT * FROM base'' , which sql will drop for improper syntax @MehdiBounya – datasci Apr 08 '18 at 17:15
  • 2
    We are taking the comments section too far... But trust me, your code **is** vulnerable to SQL injection. – Spoody Apr 08 '18 at 17:17
  • 2
    This whole question and answer is weird. You need to listen to @MehdiBounya's advice about your injection problems. You mention JS so it shouldn't happen, but how does JS store in the DB? It doesn't, so it has no place in this argument. Honestly, your code is vulnerable, and your design approach is going to drive you insane. – James Apr 08 '18 at 17:23

1 Answers1

-2

Figured this out as I was submitting lol!

So if you need double quotes around your text, then use ''$variable''

(singlequote)(singlequote)$variable(singlequote)(singlequote)
datasci
  • 61
  • 1
  • 8
  • 2
    No, wait... what? – Spoody Apr 08 '18 at 16:18
  • "*So if you need double quotes around your text*" but you put "*(singlequote)*".. so do you mean double quotes `"` or double quotes as in 2 lots of single quotes `' ' `? – James Apr 08 '18 at 17:30