All Questions
49,405
questions
2773
votes
27
answers
2.2m
views
How can I prevent SQL injection in PHP?
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:
$unsafe_variable = $_POST['user_input'];
...
847
votes
38
answers
1.3m
views
How do I get the query builder to output its raw SQL query as a string?
Given the following code:
DB::table('users')->get();
I want to get the raw SQL query string that the database query builder above will generate. In this example, it would be SELECT * FROM users.
...
- 11.4k
786
votes
4
answers
313k
views
SQL injection that gets around mysql_real_escape_string()
Is there an SQL injection possibility even when using mysql_real_escape_string() function?
Consider this sample situation. SQL is constructed in PHP like this:
$login = mysql_real_escape_string(...
- 82.4k
422
votes
19
answers
671k
views
Get table column names in MySQL?
Is there a way to grab the columns name of a table in MySQL using PHP?
- 6,248
347
votes
19
answers
861k
views
Cannot simply use PostgreSQL table name ("relation does not exist")
I'm trying to run the following PHP script to do a simple database query:
$db_host = "localhost";
$db_name = "showfinder";
$username = "user";
$password = "password";
$dbconn = pg_connect("host=$...
- 5,100
227
votes
3
answers
354k
views
How to insert multiple rows from a single query using eloquent/fluent
I have the following query:
$query = UserSubject::where('user_id', Auth::id())->select('subject_id')->get();
and as expected I get the following result:
[{"user_id":8,"subject_id":9},{"...
- 2,923
204
votes
5
answers
148k
views
Make column not nullable in a Laravel migration
I'm writing a migration to make certain columns in a table nullable right now. For the down function, I of course want to make those columns not nullable again. I looked through the schema builder ...
- 7,499
196
votes
9
answers
412k
views
selecting unique values from a column
I have a MySQL table which contains the following type of information:
Date product
2011-12-12 azd
2011-12-12 yxm
2011-12-10 sdx
2011-12-10 ...
- 2,477
182
votes
10
answers
354k
views
Increment value in MySQL update query
I have made this code for giving out +1 point, but it doesn't work properly.
mysql_query("
UPDATE member_profile
SET points= ' ".$points." ' + 1
WHERE user_id = '".$...
- 17.9k
166
votes
16
answers
138k
views
Getting raw SQL query string from PDO prepared statements
Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.
- 33k
149
votes
19
answers
165k
views
How to debug PDO database queries?
Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it ...
- 124k
132
votes
11
answers
70k
views
Flat file databases [closed]
What are the best practices around creating flat file database structures in PHP?
A lot of more matured PHP flat file frameworks out there which I attempt to implement SQL-like query syntax which is ...
- 6,197
130
votes
17
answers
390k
views
How to print SQL statement in codeigniter model
I have a sql statement in my model,
I then say
$query = $this->db->query($sql, array(fields, fields1);
if ($query) {
return true:
} else {
echo "failed";
return false;
}
My query ...
- 4,931
127
votes
11
answers
69k
views
How to apply bindValue method in LIMIT clause?
Here is a snapshot of my code:
$fetchPictures = $PDO->prepare("SELECT *
FROM pictures
WHERE album = :albumId
ORDER BY id ASC
LIMIT :skip, :max");
$fetchPictures->bindValue('...
- 48.6k
127
votes
24
answers
287k
views
Commands out of sync; you can't run this command now
I am trying to execute my PHP code, which calls two MySQL queries via mysqli, and get the error "Commands out of sync; you can't run this command now".
Here is the code I am using
<?php
$con = ...
- 4,131
124
votes
5
answers
534k
views
eloquent laravel: How to get a row count from a ->get()
I'm having a lot of trouble figuring out how to use this collection to count rows.
$wordlist = \DB::table('wordlist')->where('id', '<=', $correctedComparisons)
->get();
I ...
- 1,805
119
votes
18
answers
299k
views
Display an array in a readable/hierarchical format [duplicate]
Here is the code for pulling the data for my array
<?php
$link = mysqli_connect('localhost', 'root', '', 'mutli_page_form');
$query = "SELECT * FROM wills_children WHERE will=73";
$...
- 8,324
119
votes
10
answers
237k
views
Execute raw SQL using Doctrine 2
I want to execute raw SQL using Doctrine 2
I need to truncate the database tables and initialize tables with default test data.
- 85.9k
115
votes
5
answers
346k
views
How to execute raw queries with Laravel 5.1?
So I have this tiny query to run on my DB and it works fine in MySQL Workbench.
Basically, a SELECT with LEFT JOIN and UNION with LEFT JOIN again.
SELECT
cards.id_card,
cards.hash_card,
...
- 4,540
114
votes
4
answers
194k
views
Laravel Advanced Wheres how to pass variable into function?
Example in doc:
DB::table('users')
->whereExists(function($query)
{
$query->select(DB::raw(1))
->from('orders')
->whereRaw('...
- 1,684
108
votes
8
answers
198k
views
Checking for an empty field with MySQL
I've wrote a query to check for users with certain criteria, one being they have an email address.
Our site will allow a user to have or not have an email address.
$aUsers=$this->readToArray('
...
user275074
106
votes
2
answers
8k
views
Cleansing User Passwords
How should I escape or cleanse user-provided passwords before I hash them and store them in my database?
When PHP developers consider hashing users' passwords for security purposes, they often tend ...
- 34.3k
101
votes
6
answers
177k
views
How to separate DATE and TIME from DATETIME in MySQL?
I am storing a DATETIME field in a table. Each value looks something like this:
2012-09-09 06:57:12
I am using this syntax:
date("Y-m-d H:i:s");
Now my question is, while fetching the ...
- 1,053
97
votes
5
answers
97k
views
How to properly set up a PDO connection
From time to time I see questions regarding connecting to database.
Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I've never thought about it because the ...
- 2,220
94
votes
5
answers
147k
views
SQL Query: order by length of characters?
Is it possible to order sql data rows by the total number of characters?
e.g. SELECT * FROM database ORDER BY data.length()
- 1,279
94
votes
5
answers
62k
views
Best way to get result count before LIMIT was applied
When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls.
Currently I do that by running the query twice, once wrapped in a count()...
- 8,260
93
votes
4
answers
184k
views
Doctrine 2: Update query with query builder
Hi
I've got the following query but it doesn't seem to work.
$q = $this->em->createQueryBuilder()
->update('models\User', 'u')
->set('u.username', $username)
->set('u.email'...
- 953
90
votes
3
answers
341k
views
Simple PHP Pagination script [closed]
I have rows of data coming from database, I would like to have a table with a simple pagination, what is the easiest way of doing it?
I'd be glad if anyone could provide.
- 6,035
90
votes
2
answers
43k
views
Can I parameterize the table name in a prepared statement? [duplicate]
I've used the mysqli_stmt_bind_param function several times. However, if I separate variables that I'm trying to protect against SQL injection I run into errors.
Here's some code sample:
function ...
- 1,362
88
votes
4
answers
146k
views
How to use andWhere and orWhere in Doctrine?
WHERE a = 1 AND (b = 1 Or b = 2) AND (c = 1 OR c = 2)
How can i make this in Doctrine?
$q->where("a = 1");
$q->andWhere("b = 1")
$q->orWhere("b = 2")
$q->andWhere("c = 1")
$q->orWhere(...
- 2,259
87
votes
4
answers
752k
views
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
I've looked through all the other StackOverflow (and google) posts with the same problem, but none seemed to address my problem.
I am using PDO and PHP.
My code:
$vals = array(
':from' => $...
- 2,078
83
votes
5
answers
455k
views
How to include a PHP variable inside a MySQL statement
I'm trying to insert values in the contents table. It works fine if I do not have a PHP variable inside VALUES. When I put the variable $type inside VALUES then this doesn't work. What am I doing ...
- 10.2k
80
votes
5
answers
94k
views
How can I search for slash (\) in MySQL? And why is escaping (\) not required for where (=), but for Like it is required?
Consider this QUERY (DEMO IS HERE)
(SELECT * FROM `titles` where title = 'test\\')
UNION ALL
(SELECT * FROM `titles` where title LIKE 'test\\\\')
Output:
| ID | TITLE |
--------------
| 1 | test\ |...
user1646111
79
votes
31
answers
161k
views
Loading .sql files from within PHP
I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in ...
- 48.4k
76
votes
11
answers
148k
views
How to resolve ambiguous column names when retrieving results?
I have two tables in my database:
NEWS table with columns:
id - the news id
user - the user id of the author)
USERS table with columns:
id - the user id
I want to execute this SQL:
SELECT * ...
- 9,992
72
votes
7
answers
207k
views
Laravel: getting a single value from a MySQL query
I'm trying get a single value from MySQL database using laravel but the problem I'm getting an array . this is my query result in MySQL command line:
select groupName from users;
+-----------+
| ...
- 1,505
72
votes
2
answers
190k
views
Laravel Eloquent how to use between operator
I am trying to find an elegant way in Eloquent and Laravel to say
select * from UserTable where Age between X and Y
Is there a between operator in Eloquent (I can't find it).
The closest i have ...
- 4,689
70
votes
4
answers
63k
views
Examples of SQL Injections through addslashes()?
In PHP, I know that mysql_real_escape is much safer than using addslashes.
However, I could not find an example of a situation where addslashes would let an SQL Injection happen.
Can anyone give some ...
- 48.6k
67
votes
18
answers
173k
views
Laravel Eloquent get results grouped by days
I currently have a table of page_views that records one row for each time a visitor accesses a page, recording the user's ip/id and the id of the page itself. I should add that the created_at column ...
- 1,713
66
votes
4
answers
69k
views
Run raw SQL in migration [duplicate]
I'm trying to execute a raw SQL thru a Laravel (PHP) migration execution.
I was trying with whatever syntax and can't think how can I write this correctly:
Schema::table('users', function(Blueprint $...
- 7,067
65
votes
7
answers
97k
views
Check If a Column Exists in Laravel Migration File
Already I have a table name table_one. Now I want to add two more columns to it. Everything works fine so far. But in my method, I want to check a column exists or not in my table like dropIfExists('...
- 3,007
64
votes
26
answers
73k
views
How can I compare two sets of 1000 numbers against each other?
I must check approximately 1000 numbers against 1000 other numbers.
I loaded both and compared them server-side:
foreach( $numbers1 as $n1 ) {
foreach( $numbers2 as $n2 ) {
if( $n1 == $n2 ) {
...
- 2,165
64
votes
4
answers
149k
views
What is parameterized query? [duplicate]
What is a parameterized query, and what would an example of such a query be in PHP and MySQL?
- 677
63
votes
6
answers
44k
views
Do SQL connections opened with PDO in PHP have to be closed
When I open a MySQL connection in PHP with just PHP's built-in MySQL functions, I do the following:
$link = mysql_connect($servername, $username, $password);
mysql_select_db($dbname);
//queries ...
- 4,716
59
votes
2
answers
69k
views
How can I with mysqli make a query with LIKE and get all results?
This is my code but it dosn't work:
$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE ?");
$stmt->bind_param("s"...
- 1,331
57
votes
18
answers
77k
views
A script to change all tables and fields to the utf-8-bin collation in MYSQL
Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database?
I can write one myself, but I think that this should be something that ...
user19302
57
votes
4
answers
140k
views
Concatenate string with field value in MySQL [duplicate]
I have the need to concatenate a string with a field value in a MySQL query in order to LEFT JOIN two tables.
Table one has a column called "category_id" with numeric values, such as 61, 78, 94 and ...
- 903
57
votes
7
answers
80k
views
MySQL INTO OUTFILE override existing file?
I've written a big sql script that creates a CSV file. I want to call a cronjob every night to create a fresh CSV file and have it available on the website.
Say for example I'm store my file in '/...
- 8,393
55
votes
20
answers
214k
views
PHP: maximum execution time when importing .SQL data file
I am trying to import a large .sql data file using phpMyAdmin in XAMPP. However this is taking a lot of time and I keep getting:
Fatal error: Maximum execution time of 300 seconds exceeded in C:\...
- 651
55
votes
3
answers
44k
views
ALTER TABLE in Magento setup script without using SQL
Jonathon Day says
"updates SHOULD NOT be in the form of
SQL commands". I haven't come across
any DDL or DML statments that cannot
be executed via Magento's config
structures.
(In the ...
- 37.7k